package com.sxfq.loansupermarket.backsystem.modular.tcCenter.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sxfq.loansupermarket.backsystem.common.constant.factory.PageFactory;
import com.sxfq.loansupermarket.backsystem.common.controller.BaseController;
import com.sxfq.loansupermarket.backsystem.common.exception.BizExceptionEnum;
import com.sxfq.loansupermarket.backsystem.common.exception.BussinessException;
import com.sxfq.loansupermarket.backsystem.common.persistence.model.Dept;
import com.sxfq.loansupermarket.backsystem.core.log.LogManager;
import com.sxfq.loansupermarket.backsystem.core.log.factory.LogTaskFactory;
import com.sxfq.loansupermarket.backsystem.core.shiro.ShiroKit;
import com.sxfq.loansupermarket.backsystem.core.support.DateTimeKit;
import com.sxfq.loansupermarket.backsystem.core.support.HttpKit;
import com.sxfq.loansupermarket.backsystem.core.util.CommUtils;
import com.sxfq.loansupermarket.backsystem.core.util.StringUtil;
import com.sxfq.loansupermarket.backsystem.core.util.ToolUtil;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.entity.MembersUser;
import com.sxfq.loansupermarket.backsystem.modular.IntegralMall.service.MemberProductService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.CustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.FirstCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.ScoreDealInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.entity.SearchCustomerInfo;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerPublicService;
import com.sxfq.loansupermarket.backsystem.modular.arrival.service.ICustomerService;
import com.sxfq.loansupermarket.backsystem.modular.base.SqlMapper;
import com.sxfq.loansupermarket.backsystem.modular.basicData.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.CaseInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.DoctorOperationInfo;
import com.sxfq.loansupermarket.backsystem.modular.doctorLooked.entity.ToothSite;
import com.sxfq.loansupermarket.backsystem.modular.marketing.entity.ReservationsInfo;
import com.sxfq.loansupermarket.backsystem.modular.marketing.service.IReservationsService;
import com.sxfq.loansupermarket.backsystem.modular.refunds.entity.RechargeInfo;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.entity.*;
import com.sxfq.loansupermarket.backsystem.modular.tcCenter.service.IConsultantService;
import net.sf.json.JSONArray;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author py
 * @Date 2018年4月19日23:15:54
 */
@Controller
@RequestMapping("/consultant")
public class ConsultantController extends BaseController {
    @Autowired
    IConsultantService service;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private MemberProductService memberProductService;

    @Autowired
    ICustomerService customerService;

    @Autowired
    IReservationsService reservationsService;

    @Autowired
    ICustomerPublicService customerPublicService;

    @Autowired
    private SqlMapper sqlMapper;

    private String PREFIX = "/tcCenter/consultant/";


    /**
     * 跳转到人脸识别全部患者
     */
    @RequestMapping("/faceRecognition_whole")
    public String faceRecognition_whole(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "consultant_faceRecognitionWhole.html";
    }

    /**
     * 跳转到TC-收费详细-开单详情
     */
    @RequestMapping("/openingDetails/{operationTime}/{customerId}/{reservationsId}/{outpatientName}")
    public String openingDetails(@PathVariable(value = "operationTime") String operationTime, @PathVariable(value = "customerId") String customerId,
                                 @PathVariable(value = "reservationsId") String reservationsId, @PathVariable(value = "outpatientName") String outpatientName, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String abbreviationSql = "select * from dept t where t.simplename='" + outpatientName + "'";
            Dept dept = sqlMapper.selectOne(abbreviationSql, Dept.class);
            String dealTables = dept.getAbbreviation() + "deal_info";
            String serviceTables = dept.getAreaAbbreviation() + "pay_service_info";
            String Sql = "select t.id2 as id2,t.doctor as doctor,t.orthodontic_number as orthodonticNumber, t1.pay_service_name as payServiceName,t1.unit as unit,t.pay_service as payService,t.pay_service_num as payServiceNum," +
                    "t.left_up as leftUp,t.left_down as leftDown,t.right_up as rightUp,t.right_down as rightDown,t.pay_service*t.pay_service_num as primaryAmount," +
                    "t.order_discount as orderDiscount,t.deduction_typeName as deductionTypeName,whole_discount as wholeDiscount,GROUP_CONCAT( distinct (t.doctor)) as doctorAll," +
                    "sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay,sum(t.deduction_amount) as deductionAmount,GROUP_CONCAT( distinct (t.tc_name)) as tcNameAll," +
                    "sum(t.deposit_amount) as depositAmount,sum(t.arrears) as arrears from " + dealTables + " t left join " + serviceTables + " t1 " +
                    "on t.pay_service_id = t1.id where t.operation_time = '" + operationTime + "' and t.reservations_id = '" + reservationsId + "' " +
                    "and t.deal_status = '已结账'  group by t.id2 order by t.operation_time desc";
            List<PayServicedInfo> payServiceList = sqlMapper.selectList(Sql, PayServicedInfo.class);
            Double taotalDeductionAmount = 0.00;
            Double taotalDepositAmount = 0.00;
            Double taotalArrears = 0.00;
            Double taotalFoldingPay = 0.00;
            Double taotalServiceActualPay = 0.00;
            Double taotalPrimaryAmount = 0.00;
            String orthodonticNumber = "";
            for (int i = 0; i < payServiceList.size(); i++) {
                taotalDeductionAmount += payServiceList.get(i).getDeductionAmount();
                taotalDepositAmount += payServiceList.get(i).getDepositAmount();
                taotalArrears += payServiceList.get(i).getArrears();
                taotalFoldingPay += payServiceList.get(i).getFoldingPay();
                taotalServiceActualPay += payServiceList.get(i).getServiceActualPay();
                taotalPrimaryAmount += payServiceList.get(i).getPrimaryAmount();
                if (StringUtil.isNotEmpty(payServiceList.get(i).getOrthodonticNumber().replace(" ", ""))) {
                    orthodonticNumber = payServiceList.get(i).getOrthodonticNumber();
                }
            }
            model.addAttribute("orthodonticNumber", orthodonticNumber);
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("payServiceList", payServiceList);
            model.addAttribute("taotalDeductionAmount", taotalDeductionAmount);
            model.addAttribute("taotalDepositAmount", taotalDepositAmount);
            model.addAttribute("taotalArrears", taotalArrears);
            model.addAttribute("taotalFoldingPay", taotalFoldingPay);
            model.addAttribute("taotalServiceActualPay", taotalServiceActualPay);
            model.addAttribute("taotalPrimaryAmount", taotalPrimaryAmount);
            model.addAttribute("wholeDiscount", payServiceList.get(0).getWholeDiscount());
            model.addAttribute("tcNameAll", payServiceList.get(0).getTcNameAll());
            model.addAttribute("doctorAll", payServiceList.get(0).getDoctorAll());
            model.addAttribute("operationTime", operationTime);
            model.addAttribute("reservationsId", reservationsId);
            model.addAttribute("customerId", customerId);
            model.addAttribute("simplename", dept.getSimplename());
            return PREFIX + "consultant_openingDetails.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    @RequestMapping("/openingDetailAlone/{operationTime}/{customerId}/{reservationsId}/{outpatientName}/{getTime}")
    public String openingDetailAlone(@PathVariable(value = "operationTime") String operationTime, @PathVariable(value = "customerId") String customerId,
                                     @PathVariable(value = "reservationsId") String reservationsId, @PathVariable(value = "outpatientName") String outpatientName,
                                     @PathVariable(value = "getTime") String getTime, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String abbreviationSql = "select id,abbreviation,simplename from dept t where t.simplename='" + outpatientName + "'";
            Dept dept = sqlMapper.selectOne(abbreviationSql, Dept.class);
            String dealTables = dept.getAbbreviation() + "deal_info";
            String Sql = "select t.id2 as id2,t.doctor as doctor,t.orthodontic_number as orthodonticNumber, t1.pay_service_name as payServiceName,t1.unit as unit,t.pay_service as payService,t.pay_service_num as payServiceNum," +
                    "t.left_up as leftUp,t.left_down as leftDown,t.right_up as rightUp,t.right_down as rightDown,t.pay_service*t.pay_service_num as primaryAmount," +
                    "t.order_discount as orderDiscount,t.deduction_typeName as deductionTypeName,whole_discount as wholeDiscount,GROUP_CONCAT( distinct (t.doctor)) as doctorAll," +
                    "t.folding_pay as foldingPay,sum(t.service_actual_pay) as serviceActualPay,sum(t.deduction_amount) as deductionAmount,GROUP_CONCAT( distinct (t.tc_name)) as tcNameAll," +
                    "sum(t.deposit_amount) as depositAmount,sum(t.arrears) as arrears from " + dealTables + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 " +
                    "on t.pay_service_id = t1.id where t.operation_time = '" + operationTime + "' and t.reservations_id = '" + reservationsId + "' " +
                    "and t.deal_status = '已结账' and t.get_time='" + getTime + "'  group by t.id2 order by t.operation_time desc";
            List<PayServicedInfo> payServiceList = sqlMapper.selectList(Sql, PayServicedInfo.class);
            Double taotalDeductionAmount = 0.00;
            Double taotalDepositAmount = 0.00;
            Double taotalArrears = 0.00;
            Double taotalFoldingPay = 0.00;
            Double taotalServiceActualPay = 0.00;
            Double taotalPrimaryAmount = 0.00;
            for (int i = 0; i < payServiceList.size(); i++) {
                taotalDeductionAmount += payServiceList.get(i).getDeductionAmount();
                taotalDepositAmount += payServiceList.get(i).getDepositAmount();
                taotalArrears += payServiceList.get(i).getArrears();
                taotalFoldingPay += payServiceList.get(i).getFoldingPay();
                taotalServiceActualPay += payServiceList.get(i).getServiceActualPay();
                taotalPrimaryAmount += payServiceList.get(i).getPrimaryAmount();
            }
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("payServiceList", payServiceList);
            model.addAttribute("taotalDeductionAmount", taotalDeductionAmount);
            model.addAttribute("taotalDepositAmount", taotalDepositAmount);
            model.addAttribute("taotalArrears", taotalArrears);
            model.addAttribute("taotalFoldingPay", taotalFoldingPay);
            model.addAttribute("taotalServiceActualPay", taotalServiceActualPay);
            model.addAttribute("taotalPrimaryAmount", taotalPrimaryAmount);
            model.addAttribute("wholeDiscount", payServiceList.get(0).getWholeDiscount());
            model.addAttribute("tcNameAll", payServiceList.get(0).getTcNameAll());
            model.addAttribute("doctorAll", payServiceList.get(0).getDoctorAll());
            model.addAttribute("orthodonticNumber", payServiceList.get(0).getOrthodonticNumber());
            model.addAttribute("operationTime", operationTime);
            model.addAttribute("reservationsId", reservationsId);
            model.addAttribute("customerId", customerId);
            model.addAttribute("simplename", dept.getSimplename());
            return PREFIX + "consultant_openingDetails.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    /**
     * 修改正畸编号
     */
    @RequestMapping(value = "/updateOrthodonticNumber", method = RequestMethod.POST)
    @ResponseBody
    public int updateOrthodonticNumber(@RequestBody List<PaymentInfo> list, Model model) {
        PaymentInfo info = null;
        for (int i = 0; i < list.size(); i++) {
            info = list.get(i);
            String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " set left_up='" + info.getLeftUp() + "'" +
                    ",left_down='" + info.getLeftDown() + "',right_up='" + info.getRightUp() + "',right_down='" + info.getRightDown() + "' " +
                    " where operation_time='" + info.getOperationTime() + "' and reservations_id='" + info.getReservationsId() + "'" +
                    " and id2='" + info.getId2() + "'; ";
            sqlMapper.update(sql);
            String sql2 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " set orthodontic_number='" + info.getOrthodonticNumber() + "'" +
                    " where operation_time='" + info.getOperationTime() + "' and reservations_id='" + info.getReservationsId() + "'" +
                    " and id2='" + info.getId2() + "' " +
                    " and pay_service_id in (select id from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " where first_level_project like '%正畸%')";
            sqlMapper.update(sql2);
        }
        String sql11 = "select id,customer_name as customerName ,customer_phone as customerPhone from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " where arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null " +
                " and reservations_id='" + info.getReservationsId() + "' limit 0,1 ";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql11, CustomerInfo.class);
        if (StringUtil.isNotEmpty(info.getOrthodonticNumber())) {
            //插入正畸编号日志
            String sq11 = "INSERT INTO `orthodontics_number_log` (createtime,single_person, customer_name,customer_phone," +
                    " `succeed`,outpatient_name ,orthodontic_number,customer_id,reservations_id) VALUES " +
                    " ('" + DateTimeKit.now() + "','" + ShiroKit.getUser().getAccount() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerPhone() + "','成功'," +
                    "'" + ShiroKit.getUser().getOutpatientName() + "','" + info.getOrthodonticNumber() + "','" + info.getCustomerId() + "','" + info.getReservationsId() + "');";
            sqlMapper.insert(sq11);
        }
        return 1;
    }

    /**
     * 跳转到变更医生
     */
    @RequestMapping("/updateAchievement/{operationTime}/{customerId}/{reservationsId}")
    public String updateDoctor(@PathVariable(value = "operationTime") String operationTime, @PathVariable(value = "customerId") String customerId,
                               @PathVariable(value = "reservationsId") String reservationsId, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String Sql = "select t.id2 as id2, t1.pay_service_name as payServiceName,t.outpatient_name as outpatientName,t.doctor as doctor," +
                " sum(t.folding_pay) as foldingPay from (" + dealTables + ") t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 " +
                " on t.pay_service_id = t1.id where t.operation_time = '" + operationTime + "' and t.reservations_id = '" + reservationsId + "' " +
                " and t.deal_status = '已结账' group by t.id2 order by t.operation_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(Sql, PayServicedInfo.class);
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0) == null) {
            String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                    " where b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
            List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "DoctorInfoList", doctorInfoList);
        }
        model.addAttribute("doctorList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0));
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "nursesLists", 0) == null) {
            String getListSql = " select a.id as id,a.name as nurse from user a left join dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where a.status='1' and (b.abbreviation='" + tableHead + "' and c.name in ('护士','护士长','前台人员') and a.`status`='1')";
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "nursesLists", sqlMapper.selectList(getListSql));
        }
        model.addAttribute("nursesList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "nursesLists", 0));
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("payServiceList", payServiceList);
        model.addAttribute("tcNameAll", payServiceList.get(0).getTcNameAll());
        model.addAttribute("doctorAll", payServiceList.get(0).getDoctorAll());
        return PREFIX + "consultant_updateDoctor.html";
    }

    /**
     * 跳转到变更医生
     */
    @RequestMapping("/updateDoctorDeal/{getTime}/{customerId}/{reservationsId}")
    public String updateDoctorDeal(@PathVariable(value = "getTime") String getTime, @PathVariable(value = "customerId") String customerId,
                                   @PathVariable(value = "reservationsId") String reservationsId, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String Sql = "select t.id2 as id2, t1.pay_service_name as payServiceName,t.outpatient_name as outpatientName,t.doctor as doctor," +
                " sum(t.folding_pay) as foldingPay from (" + dealTables + ") t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 " +
                " on t.pay_service_id = t1.id where t.get_time = '" + getTime + "' and t.reservations_id = '" + reservationsId + "' " +
                " and t.deal_status = '已结账' group by t.id2 order by t.operation_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(Sql, PayServicedInfo.class);
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0) == null) {
            String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                    " where b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
            List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "DoctorInfoList", doctorInfoList);
        }
        model.addAttribute("doctorList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0));
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("payServiceList", payServiceList);
        model.addAttribute("getTime", getTime);
        model.addAttribute("tcNameAll", payServiceList.get(0).getTcNameAll());
        model.addAttribute("doctorAll", payServiceList.get(0).getDoctorAll());
        return PREFIX + "consultant_updateDealDoctor.html";
    }

    //修改医生
    @RequestMapping(value = "/updateDoctorDealOK", method = RequestMethod.POST)
    @ResponseBody
    public int choiceDoctor(String reservationsId, String doctor, String getTime) throws Exception {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sq1 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " a set a.doctor='" + doctor + "' " +
                " where reservations_id='" + reservationsId + "' and get_time='" + getTime + "'";
        return sqlMapper.update(sq1);
    }

    /**
     * 跳转到修改开单备注
     */
    @RequestMapping("/updateRemarksDeal/{getTime}/{customerId}/{reservationsId}")
    public String updateRemarksDeal(@PathVariable(value = "getTime") String getTime, @PathVariable(value = "customerId") String customerId,
                                    @PathVariable(value = "reservationsId") String reservationsId, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sq1 = "select remarks from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " a " +
                " where reservations_id='" + reservationsId + "' and get_time='" + getTime + "' limit 1";
        RechargeInfo info = sqlMapper.selectOne(sq1, RechargeInfo.class);
        model.addAttribute("getTime", getTime);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("remarks", info.getRemarks());
        return PREFIX + "consultant_updateRemarksDeal.html";
    }

    //修改开单备注
    @RequestMapping(value = "/updateDealRemarksOk", method = RequestMethod.POST)
    @ResponseBody
    public int updateDealRemarksOk(String reservationsId, String remarks, String getTime, String customerId) throws Exception {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sq1 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " a set a.remarks='" + remarks + "' " +
                " where reservations_id='" + reservationsId + "' and get_time='" + getTime + "'";
        return sqlMapper.update(sq1);
    }

    /**
     * 跳转到TC-收费详细-退款
     */
    @RequestMapping("/refundTotal/{operationTime}/{reservationsId}/{customerId}")
    public String refundTotal(@PathVariable(value = "operationTime") String operationTime, @PathVariable(value = "reservationsId") String reservationsId,
                              @PathVariable(value = "customerId") String customerId, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String Sql = "select t.id2 as id2, t1.pay_service_name as payServiceName,t.pay_service as payService,t.pay_service_num as payServiceNum,t2.type as type ," +
                "sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay,sum(t.deduction_amount) as deductionAmount,t.deduction_typeName as deductionTypeName," +
                "sum(t.deposit_amount) as depositAmount,sum(t.arrears) as arrears,t.referraler_person as referralerPerson,t.referraler_remarks as referralerRemarks,t.referraler_personed as referralerPersoned,t.referraler_time as referralerTime" +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 " +
                "on t.pay_service_id = t1.id left join checkout_method_info t2 on t.deduction_type=t2.id " +
                "where t.operation_time = '" + operationTime + "' and t.reservations_id = '" + reservationsId + "' and t.deal_status = '已结账' group by t.id2 order by t.operation_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(Sql, PayServicedInfo.class);
        Double totoalFoldingPay = 0.00;
        Double totoalServiceActualPay = 0.00;
        for (int i = 0; i < payServiceList.size(); i++) {
            totoalFoldingPay = totoalFoldingPay + payServiceList.get(i).getFoldingPay();
            totoalServiceActualPay = totoalServiceActualPay + payServiceList.get(i).getServiceActualPay() + payServiceList.get(i).getDepositAmount();
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='" + tableHead + "' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList = sqlMapper.selectList(getListSql, TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("payServiceList", payServiceList);
        model.addAttribute("customerId", customerId);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("totoalFoldingPay", totoalFoldingPay);
        model.addAttribute("totoalServiceActualPay", totoalServiceActualPay);
        return PREFIX + "consultant_refundTotal.html";
    }

    /**
     * 跳转到TC看诊
     */
    @RequestMapping("")
    public String consultant(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_grade_info", 0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='" + tableHead + "' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList = sqlMapper.selectList(getListSql, TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        return PREFIX + "consultant_examination.html";
    }

    @RequestMapping(value = "/select_earnestMoney/{amount}/{deductionAmount}/{totaldDepositAmount}/{payServiceId}/{reservationsId}")
    public String toAddVisited(@PathVariable(value = "amount") Double amount, @PathVariable(value = "payServiceId") String payServiceId,
                               @PathVariable(value = "deductionAmount") Double deductionAmount, @PathVariable(value = "totaldDepositAmount") Double totaldDepositAmount,
                               @PathVariable(value = "reservationsId") String reservationsId, Model model) {
        if (ToolUtil.isEmpty(amount)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(deductionAmount)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(totaldDepositAmount)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(payServiceId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select t.id2 as id,sum(t.folding_pay) as foldingPay, t1.pay_service_name as payServiceName from  " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " " +
                "t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 on t.pay_service_id = t1.id where t1.is_earnest_money = 1 and t.deal_status = '已结账' and t.reservations_id = '" + reservationsId + "' group by t.id2";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        model.addAttribute("amount", amount);
        model.addAttribute("deductionAmount", deductionAmount);
        model.addAttribute("payServiceId", payServiceId);
        model.addAttribute("totaldDepositAmount", totaldDepositAmount);
        model.addAttribute("payServiceInfoList", JSONArray.fromObject(payServiceInfoList).toString());
        return PREFIX + "earnestMoney_list.html";
    }

    /**
     * 成交记录跳转到修改医生
     */
    @RequestMapping(value = "/to_updateDoctorOrNurse/{cri}")
    public String to_updateDoctorOrNurse(@PathVariable String cri, Model model) {
        String reservationsId = cri.split(",")[0];
        String operationTime = cri.split(",")[1];
        String getTime = cri.split(",")[2];
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName1 = tableHead + "deal_info";
        String sql = "select t.id as dealId,p.pay_service_name as payServiceName,t.*,p.* from " + tableName1 + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " p on(p.id=t.pay_service_id) " +
                " where t.operation_time='" + operationTime + "' and t.get_time='" + getTime + "' and t.reservations_id='" + reservationsId + "'";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        model.addAttribute("payServiceInfoList", payServiceInfoList);
        return PREFIX + "updateDoctor.html";
    }

    /**
     * 成交记录跳转到修改医生
     */
    @RequestMapping(value = "/update_doctorOrNurse", method = RequestMethod.POST)
    @ResponseBody
    public int update_doctorOrNurse(@RequestBody List<PayServicedInfo> list, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        for (int i = 0; i < list.size(); i++) {
            PayServicedInfo info = list.get(i);
            String sql = "update " + tableName1 + " t set nurse='" + info.getNurse() + "',doctor='" + info.getDoctor()
                    + "',orthodontic_number='" + info.getOrthodonticNumber() + "' where t.id2='" + info.getDealId() + "'";
            sqlMapper.update(sql);
        }
        return 1;
    }

    /**
     * 设置会员卡号
     */
    @RequestMapping(value = "/settingMembersNum/{customerId}/{reservationsId}/{membersNum}/{phone}", method = RequestMethod.POST)
    @ResponseBody
    public int settingSure(@PathVariable String customerId, @PathVariable String reservationsId, @PathVariable String membersNum, @PathVariable String phone, Model model) {
        String selesql = "select reservations_id,account,phone from members_user where reservations_id='" + reservationsId + "'";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(selesql, ConsultantInfo.class);
        if (consultantInfo == null) {
            String insql = "insert into members_user (reservations_id,account,name,password,phone,medical_record_number,address,money_balance,members_num,abbreviation,sex,age) " +
                    " select reservations_id,'" + phone + "',customer_name,123,customer_phone,medical_record_number,address,money_balance,'" + membersNum + "','" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "',customer_sex,customer_age " +
                    " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " where id='" + customerId + "';";
            sqlMapper.insert(insql);
        } else {
            String upsql = "update members_user set members_num='" + membersNum + "' where reservations_id = '" + reservationsId + "'";
            sqlMapper.update(upsql);
            return 22;
        }
        String reservationssql = "update reservations_info set members_num='" + membersNum + "' where id = '" + reservationsId + "'";
        sqlMapper.update(reservationssql);
        return 11;
    }

    /**
     * 得到客户状态
     */
    @RequestMapping(value = "/getCustomerStatus/{customerId}", method = RequestMethod.POST)
    @ResponseBody
    public String getCustomerStatus(@PathVariable String customerId, Model model) {
        String sql = "select id,customer_status as customerStatus,left(arrival_time,10) as arrivalTime,reservations_id as reservationsId,left(now(),10) as nowTime,field_tc as fieldTc " +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t where t.id='" + customerId + "'";
        CustomerInfo customerInfo = sqlMapper.selectOne(sql, CustomerInfo.class);
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String sql1 = "select id from " + tableName1 + " t where t.reservations_id='" + customerInfo.getReservationsId() + "' " +
                "and deal_status!='已结账' ";
        List<RechargeInfo> RechargeInfo = sqlMapper.selectList(sql1, RechargeInfo.class);
        int bl = 0;
        if (!(customerInfo.getNowTime()).equals(customerInfo.getArrivalTime())) {
            bl = 2;
        } else {
            if (customerInfo != null) {
                if (!(customerInfo.getFieldTc()).equals(ShiroKit.getUser().getAccount())) { //此客户tc不是当前登录人
                    if (((redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "RoleName", 0) + "").indexOf("前台") != -1 && "待定TC".equals(customerInfo.getFieldTc())) ||
                            (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "RoleName", 0) + "").indexOf("管理员") != -1 || Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Dealyes", 0) + "") == 1 || Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "IsRefundAdmin", 0) + "") == 1) {
                        bl = 0;
                    } else {
                        bl = 3;
                    }
                }
            } else {
                bl = 2;
            }
        }
        return customerInfo.getCustomerStatus() + "," + RechargeInfo.size() + "," + bl + "," + customerInfo.getFieldTc();
    }

    /**
     * 重开此单
     */
    @RequestMapping(value = "/reDeal", method = RequestMethod.POST)
    @ResponseBody
    public int reDeal(String reservationsId, String customerId, String operationTime, String getTime, String department, Model model) {
        String scsql = "select id from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " where reservations_id='" + reservationsId + "' and left(arrival_time,10)=left(now(),10)" +
                " and treatment_unit='" + department + "' limit 1";
        SfInfo infos = sqlMapper.selectOne(scsql, SfInfo.class);
        if (infos == null) {
            return 222;
        }
        String sql1 = "select id from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " where reservations_id = '" + reservationsId + "' and operation_time='" + operationTime + "' " +
                " and get_time = '" + getTime + "' and deal_status='已结账'";
        List<RechargeInfo> list = sqlMapper.selectList(sql1, RechargeInfo.class);
        if (list.size() > 0) {//此单已结账tc界面未刷新
            return 111;
        }
        String sql2 = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " set deal_status='已开单' " +
                " where id='" + customerId + "' ";
        sqlMapper.update(sql2);
        String time = DateTimeKit.now();
        if (getTime.equals(operationTime)) {
            String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " set operation_time='" + time + "',get_time='" + time + "',customer_id='" + customerId + "'" +
                    " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "' and get_time='" + getTime + "'";
            return sqlMapper.update(sql);
        } else {
            String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " set get_time='" + time + "',customer_id='" + customerId + "'" +
                    " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "' and get_time='" + getTime + "'";
            return sqlMapper.update(sql);
        }
    }

    /**
     * 开单t跳转打印
     *
     * @param customerId
     * @param model
     * @return
     */
    @RequestMapping(value = "/singlePrint/{customerId}/{operationTime}")
    public String singlePrint(@PathVariable String customerId, @PathVariable String operationTime, Model model) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName1 = tableHead + "deal_info";
        //查询当前修改 记录的客户对应所有的临时开单记录给前端
        String sql2 = "select * from " + tableName1 + " t1 " +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id where " +
                "  t1.customer_id='" + customerId + "' and t1.operation_time=(select  operation_time  from " + tableName1 + " t1" +
                " where t1.customer_id='" + customerId + "'  order by operation_time desc limit 0,1)";
        List<PayServicedInfo> payServiceInfo = sqlMapper.selectList(sql2, PayServicedInfo.class);
        String sql1 = "select sum(t1.folding_pay) as totalFoldingPay,sum(t1.service_actual_pay) as totleServiceActualPay ," +
                "sum(t1.deduction_amount) as deductionAmount ,sum(t1.deposit_amount) as depositAmount," +
                "(select sum(pay_service_num*pay_service) from " + tableName1 + "  " +
                "where customer_id='" + customerId + "'" +
                " and get_time='" + operationTime + "' and operation_time='" + operationTime + "') as primaryAmount, " +
                " if((select sum(folding_pay) from " + tableName1 + " where customer_id='" + customerId + "' and get_time='" + operationTime + "' and operation_time='" + operationTime + "') is null,0," +
                " (select sum(folding_pay) from " + tableName1 + " where customer_id='" + customerId + "' and get_time='" + operationTime + "' and operation_time='" + operationTime + "')) as foldingPay, " +
                "(select sum(arrears) from " + tableName1 + "  " +
                "where customer_id='" + customerId + "' " +
                "and get_time<='" + operationTime + "' and operation_time='" + operationTime + "') as arrears, " +
                "t1.is_do as isDo,t2.customer_name as customerName,t2.field_tc as fieldTc, " +
                "GROUP_CONCAT( distinct (t1.doctor)) as doctor, " +
                "left(t1.operation_time,10) as operationTime , t1.remarks as remarks from " + tableName1 + " t1   " +
                "left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t2 on t1.customer_id=t2.id   " +
                " where t1.customer_id='" + customerId + "' and t1.get_time='" + operationTime + "' group by t1.customer_id";
        PayServiceInfo payService = sqlMapper.selectOne(sql1, PayServiceInfo.class);
        payService.setTotalFoldingPay1(payService.getTotalFoldingPay());
        payService.setTotleServiceActualPay1(payService.getTotleServiceActualPay());
        for (int i = 0; i < payServiceInfo.size(); i++) {
            PayServicedInfo info = payServiceInfo.get(i);
            info.setIntPayService(info.getPayService());
            info.setIntFoldingPay(info.getFoldingPay());
            info.setServiceActualPay(info.getServiceActualPay());
        }
        String sql22 = "select * from dept where abbreviation='" + tableHead + "'";
        Dept depts = sqlMapper.selectOne(sql22, Dept.class);
        model.addAttribute("deptInfo", depts);
        model.addAttribute("payService", payService);
        model.addAttribute("customerId", customerId);
        model.addAttribute("payServiceInfo", payServiceInfo);
        return PREFIX + "distributive_singlePrint.html";
    }

    /**
     * 开单记录确认打印
     *
     * @param
     * @param
     * @return
     */
    @RequestMapping(value = "/singlePrintt/{ids}")
    public String singlePrintt(@PathVariable String ids, Model model) {
        String customerId = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String getTime = ids.split(",")[2];
        String operationTime = ids.split(",")[3];
        String outpatientName = ids.split(",")[4];
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(getTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(outpatientName)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String getTableHeadsql = "select * from dept t where t.simplename='" + outpatientName + "'";
        Dept dept = sqlMapper.selectOne(getTableHeadsql, Dept.class);
        String tableHead = dept.getAbbreviation();
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName1 = tableHead + "deal_info";
        String tableNamep = dept.getAreaAbbreviation() + "pay_service_info";
        String tableNamed = tableHead + "customer_info";
        //查询当前修改 记录的客户对应所有的临时开单记录给前端
        String sql2 = "select * from " + tableName1 + " t1 " +
                " left join " + tableNamep + " t2 on t1.pay_service_id=t2.id where " +
                "  t1.customer_id='" + customerId + "' and t1.get_time='" + getTime + "'";
        List<PayServicedInfo> payServiceInfo = sqlMapper.selectList(sql2, PayServicedInfo.class);
        String sql1 = "select sum(t1.folding_pay) as totalFoldingPay,sum(t1.service_actual_pay) as totleServiceActualPay ," +
                "sum(t1.deduction_amount) as deductionAmount ,sum(t1.deposit_amount) as depositAmount," +
                "(select sum(pay_service_num*pay_service) from " + tableName1 + " " +
                "where reservations_id='" + reservationsId + "' and get_time='" + operationTime + "' " +
                " and operation_time='" + operationTime + "') as primaryAmount," +
                "if((select sum(folding_pay) from " + tableName1 + " where reservations_id='" + reservationsId + "' and get_time='" + operationTime + "' and operation_time='" + operationTime + "') is null,0," +
                " (select sum(folding_pay) from " + tableName1 + " where reservations_id='" + reservationsId + "' and get_time='" + operationTime + "' and operation_time='" + operationTime + "')) as foldingPay," +
                "(select sum(arrears) from " + tableName1 + " " +
                "where reservations_id='" + reservationsId + "' and get_time<='" + getTime + "' " +
                " and operation_time='" + operationTime + "') as arrears, " +
                "t1.is_do as isDo,t2.customer_name as customerName,t2.field_tc as fieldTc, " +
                "GROUP_CONCAT( distinct (t1.doctor)) as doctor, " +
                "left(t1.operation_time,10) as operationTime , t1.remarks as remarks from " + tableName1 + " t1   " +
                "left join " + tableNamed + " t2 on t1.customer_id=t2.id   " +
                " where t1.customer_id='" + customerId + "' and t1.get_time='" + getTime + "' group by t1.customer_id";
        PayServiceInfo payService = sqlMapper.selectOne(sql1, PayServiceInfo.class);
        payService.setTotalFoldingPay1(payService.getTotalFoldingPay());
        payService.setTotleServiceActualPay1(payService.getTotleServiceActualPay());
        for (int i = 0; i < payServiceInfo.size(); i++) {
            PayServicedInfo info = payServiceInfo.get(i);
            info.setIntPayService(info.getPayService());
            info.setIntFoldingPay(info.getFoldingPay());
            info.setServiceActualPay(info.getServiceActualPay());
        }
        String sql22 = "select * from dept where abbreviation='" + tableHead + "'";
        Dept depts = sqlMapper.selectOne(sql22, Dept.class);
        model.addAttribute("deptInfo", depts);
        model.addAttribute("payService", payService);
        model.addAttribute("customerId", customerId);
        model.addAttribute("payServiceInfo", payServiceInfo);
        return PREFIX + "distributive_singlePrint.html";
    }

    /**
     * 修改项目
     *
     * @param
     * @return
     */
    @RequestMapping(value = "/updateDeal/{ids}")
    public String updateDeal(@PathVariable String ids, Model model) {
        String id = ids.split(",")[0];
        String reservationsId = ids.split(",")[1];
        String operationTime = ids.split(",")[2];
        String treatmentUnit = ids.split(",")[3];
        String sql = "select t.*,t1.*,ifnull(t2.vip_payservice_id,0) as vipPayserviceId from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t " +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 on t.pay_service_id = t1.id " +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_members", 0) + " t2 on t1.id=t2.pay_service_id " +
                " and t.reservations_id=t2.reservations_id and t.operation_time=t2.operation_time " +
                " where t.reservations_id = '" + reservationsId + "' and t.operation_time = '" + operationTime + "' group by t.id";
        List<PayServicedInfo> dealInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0) == null) {
            String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                    " where b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
            List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "DoctorInfoList", doctorInfoList);
        }
        model.addAttribute("doctorList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0));
        String selectSql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t where t.reservations_id = '" + reservationsId + "' and t.operation_time = '" + operationTime + "'";
        List<PayServicedInfo> list = sqlMapper.selectList(selectSql, PayServicedInfo.class);
        String dealStatusNow = list.get(0).getDealStatus();
        if ("已结账".equals(dealStatusNow)) {
            model.addAttribute("dealStatus", "已结账");
        } else {
            model.addAttribute("dealStatus", "未成交");
        }
        CustomerInfo customerInfo = customerService.selectOneById(id);
        //获取抵扣方式
        String checkoutSql = "select id,checkout_method from checkout_method_info where type in ('1','2')";
        List<CheckoutMethodInfo> checkoutMethodList = sqlMapper.selectList(checkoutSql, CheckoutMethodInfo.class);
        String checkoutMethodHtml = "";
        for (int i = 0; i < checkoutMethodList.size(); i++) {
            checkoutMethodHtml += "<option value='" + checkoutMethodList.get(i).getId() + "'>" + checkoutMethodList.get(i).getCheckoutMethod() + "</option>";
        }
        Double totalSum1 = 0.00;
        Double realTotalSum = 0.00;
        String isDeduction = "0";
        String isDeposit = "0";
        Double originalPrice = 0.00;
        Double totalArrears = 0.00;
        Double newTotalSum = 0.00;
        for (int i = 0; i < dealInfoList.size(); i++) {
            totalSum1 += dealInfoList.get(i).getFoldingPay();
            realTotalSum += dealInfoList.get(i).getServiceActualPay();
            originalPrice += dealInfoList.get(i).getPrimaryAmount();
            totalArrears += dealInfoList.get(i).getArrears();
            newTotalSum = newTotalSum + dealInfoList.get(i).getFoldingPay() - dealInfoList.get(i).getDeductionAmount() - dealInfoList.get(i).getDepositAmount();
            if (dealInfoList.get(i).getDeductionAmount() > 0) {
                isDeduction = "1";
            }
            if (dealInfoList.get(i).getDepositAmount() > 0) {
                isDeposit = "1";
            }
        }
        //查询储值、积分余额
        String noDealSql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " where deal_status != '已结账' and reservations_id = '" + customerInfo.getReservationsId() + "'" +
                "and operation_time!='" + operationTime + "';";
        List<PayServicedInfo> noDealList = sqlMapper.selectList(noDealSql, PayServicedInfo.class);
        String jfbalancesql = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from return_cash_info where reservations_id='" + customerInfo.getReservationsId() + "'";
        ScoreDealInfo scoreDealInfo = sqlMapper.selectOne(jfbalancesql, ScoreDealInfo.class);
        Double moneyBalance = customerInfo.getMoneyBalance();
        BigDecimal b1 = new BigDecimal(Double.parseDouble(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Proportional", 0) + ""));
        BigDecimal b2 = new BigDecimal(Double.parseDouble(scoreDealInfo.getScoreBalance()));
        Double jfbalance = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        for (int i = 0; i < noDealList.size(); i++) {
            BigDecimal m1 = new BigDecimal(Double.toString(moneyBalance));
            BigDecimal m2 = new BigDecimal(Double.toString(noDealList.get(i).getDepositAmount()));
            moneyBalance = m1.subtract(m2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
            if (noDealList.get(i).getDeductionTypeName() != null) {
                if ((noDealList.get(i).getDeductionTypeName()).indexOf("积分") >= 0) {
                    BigDecimal j1 = new BigDecimal(Double.toString(jfbalance));
                    BigDecimal j2 = new BigDecimal(Double.toString(noDealList.get(i).getDeductionAmount()));
                    jfbalance = j1.subtract(j2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                }
            }
        }
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_grade_info", 0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0) == null) {
            String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                    " where b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
            List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "DoctorInfoList", doctorInfoList);
        }
        model.addAttribute("doctorList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0));
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "nursesLists", 0) == null) {
            String getListSql = " select a.id as id,a.name as nurse from user a left join dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where a.status='1' and (b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and c.name in ('护士','护士长','前台人员') and a.`status`='1')";
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "nursesLists", sqlMapper.selectList(getListSql));
        }
        String vipLevelName = "入门会员";
        Double discountDiscount = 1.00;
        VipLevelInfo vipLevelInfo = memberProductService.getVipLevel(reservationsId);
        if (vipLevelInfo == null) {
            model.addAttribute("vipLevelName", vipLevelName);
            model.addAttribute("discountDiscount", discountDiscount);
        } else {
            vipLevelName = vipLevelInfo.getVipLevelName();
            discountDiscount = (vipLevelInfo.getDiscountDiscount());
            model.addAttribute("vipLevelName", vipLevelName);
            model.addAttribute("discountDiscount", discountDiscount);
        }
        model.addAttribute("lowestPriceBill", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "LowestPriceBill", 0) + "");
        model.addAttribute("nursesList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "nursesLists", 0));
        model.addAttribute("outpatientManagementList", ShiroKit.getSessionAttr("outpatientManagementList"));
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("customerId", id);
        model.addAttribute("reservationsId", reservationsId);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("custComeFrom", customerInfo.getOneChannel());
        model.addAttribute("checkoutMethodHtml", checkoutMethodHtml);
        model.addAttribute("dealInfoList", dealInfoList);
        model.addAttribute("newTotalSum", newTotalSum);
        model.addAttribute("originalPrice", originalPrice);
        String getMoneyBalance = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' limit 1";
        MembersUser membersUser1 = sqlMapper.selectOne(getMoneyBalance, MembersUser.class);
        model.addAttribute("moneyBalance", membersUser1.getMoneyBalance());
        model.addAttribute("khmoneyBalance", membersUser1.getMoneyBalance());
        model.addAttribute("isDeduction", isDeduction);
        model.addAttribute("isDeposit", isDeposit);
        model.addAttribute("totalArrears", totalArrears);
        model.addAttribute("operationTime", operationTime);
        model.addAttribute("checkoutMethodList", checkoutMethodList);
        model.addAttribute("totalDiscount", dealInfoList.get(0).getWholeDiscount());
        model.addAttribute("totalSum1", totalSum1);
        model.addAttribute("realTotalSum", realTotalSum);
        model.addAttribute("treatmentUnit", treatmentUnit);
        model.addAttribute("jfbalance", jfbalance);
        return PREFIX + "update_bill.html";
    }

    @RequestMapping(value = "/getDealInfo", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getDealInfo(String reservationsId, String operationTime) throws Exception {
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 on t.pay_service_id=t1.id where t.reservations_id='" + reservationsId + "' and t.operation_time='" + operationTime + "';";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }

    //分配医生
    @RequestMapping(value = "/getDoctor", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> getDoctor() throws Exception {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String sql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                " where b.abbreviation='" + tableHead + "' and (c.name='医生' or c.name='洁牙师') and a.status='1' and b.simplename= '" + ShiroKit.getUser().getOutpatientName() + "'";
        List<ConsultantInfo> list = sqlMapper.selectList(sql, ConsultantInfo.class);
        return list;
    }

    //分配洁牙师
    @RequestMapping(value = "/getJys", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> getJys() throws Exception {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String sql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                " where b.abbreviation='" + tableHead + "' and c.name='洁牙师' and b.simplename= '" + ShiroKit.getUser().getOutpatientName() + "'";
        List<ConsultantInfo> list = sqlMapper.selectList(sql, ConsultantInfo.class);
        return list;
    }

    //分配护士和医生
    @RequestMapping(value = "/getNurse", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> getArchivings() throws Exception {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String sql = " select a.id as id,a.name as nurse from user a left join dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                "where a.status='1' and (b.abbreviation='" + tableHead + "' and c.name='护士' and a.`status`='1') " +
                " or (b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and c.name='医生' and a.`status`='1') " +
                " or (b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and c.name='洁牙师' and a.`status`='1') " +
                " or (b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and c.name='护士长' and a.`status`='1') ";
        List<ConsultantInfo> list = sqlMapper.selectList(sql, ConsultantInfo.class);
        return list;
    }

    //护士
    @RequestMapping(value = "/getNurses", method = RequestMethod.GET)
    @ResponseBody
    public List<ConsultantInfo> getNurses() throws Exception {
        String sql = " select a.id as id,a.name as nurse from user a left join dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                " where a.status='1' and (b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and c.name in ('护士','护士长','前台人员'));";
        List<ConsultantInfo> list = sqlMapper.selectList(sql, ConsultantInfo.class);
        return list;
    }

    /**
     * TC患者列表
     */
    @RequestMapping("/list")
    @ResponseBody
    public Object testrInfoList(SearchConsultantInfo info, HttpServletRequest request) {
        if (info.getAbbreviation() == "" || info.getAbbreviation() == null || info.getAbbreviation() == "null") {
            return null;
        } else {
            Page<ConsultantInfo> page = new PageFactory<ConsultantInfo>().defaultPage();
            try {
                info.setStartIndex(page.getSize() * (page.getCurrent() - 1));
                info.setLimitIndex(page.getSize());
                page.setCurrent(0);
                List<ConsultantInfo> list = service.selectPageListByQueryGetMap(page, info);
                if (list.size() == 0) {
                    info.setStartIndex(0);
                    list = service.selectPageListByQueryGetMap(page, info);
                }
                page.setTotal(service.getQueryPagesCount(page, info));
                page.setRecords(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return super.packForBT(page);
        }
    }

    @RequestMapping(value = "/consultant_update/{customerId}")
    @ResponseBody
    public List<ConsultantInfo> consultantUpdate(@PathVariable String customerId, Model model) throws Exception {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        return service.selectDiagnosisById(customerId);
    }

    /**
     * 验证是否有开单为结账的
     */
    @RequestMapping(value = "/verify", method = RequestMethod.POST)
    @ResponseBody
    public int verify(String customerId, String reservationsId, Model model) throws Exception {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String sql = "select * from " + tableName1 + " where customer_id='" + customerId + "' and deal_status='已开单'";
        List<PayServicedInfo> list = sqlMapper.selectList(sql, PayServicedInfo.class);
        if (list.size() > 0) {
            return 2;
        } else {
            return 1;
        }
    }

    @RequestMapping(value = "/commitArrears", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> commitArrears(@RequestParam Map<String, Object> map) {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        resultMap = service.commitArrears(map);
        return resultMap;
    }

    @RequestMapping(value = "/selectDealInfoByCheck", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> selectDealInfoByCheck(String settleCheck, String refundCheck, String arrearsCheck, String deductionCheck, String reservationsId) throws Exception {
        Map<String, Object> resultMap = new HashMap<String, Object>();
        Map<String, Object> sqlMap = new HashMap<String, Object>();
        sqlMap.put("settleCheck", settleCheck);
        sqlMap.put("refundCheck", refundCheck);
        sqlMap.put("arrearsCheck", arrearsCheck);
        sqlMap.put("deductionCheck", deductionCheck);
        sqlMap.put("reservationsId", reservationsId);
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info";
            } else {
                dealTables += "select * from " + head + "deal_info union ";
            }
        }
        sqlMap.put("tableName", "(" + dealTables + ")");
        if ("1".equals(settleCheck) && "1".equals(refundCheck) && "1".equals(arrearsCheck) && "1".equals(deductionCheck)) {
            sqlMap.put("srad", "");
        } else if ("1".equals(settleCheck) && "1".equals(refundCheck) && "1".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and (t1.arrears>=0 or (select count(tt.id) from (" + dealTables + ") tt where tt.id2= t1.id2 and tt.is_do = 4) > 0)");
        } else if ("1".equals(settleCheck) && "1".equals(refundCheck) && "0".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and (t1.arrears=0 or (select count(tt.id) from (" + dealTables + ") tt where tt.id2= t1.id2 and tt.is_do = 4) > 0)");
        } else if ("1".equals(settleCheck) && "0".equals(refundCheck) && "0".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and t1.arrears=0");
        } else if ("0".equals(settleCheck) && "1".equals(refundCheck) && "1".equals(arrearsCheck) && "1".equals(deductionCheck)) {
            sqlMap.put("srad", " and (t1.arrears>0 or t1.deduction_amount>0 or (select count(tt.id) from (" + dealTables + ") tt where tt.id2= t1.id2 and tt.is_do = 4) > 0)");
        } else if ("0".equals(settleCheck) && "1".equals(refundCheck) && "1".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and (t1.arrears>0  or (select count(tt.id) from (" + dealTables + ") tt where tt.id2= t1.id2 and tt.is_do = 4) > 0)");
        } else if ("0".equals(settleCheck) && "1".equals(refundCheck) && "0".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and (select count(tt.id) from ${tableName} tt where tt.id2= t1.id2 and tt.is_do = 4) > 0");
        } else if ("0".equals(settleCheck) && "1".equals(refundCheck) && "0".equals(arrearsCheck) && "1".equals(deductionCheck)) {
            sqlMap.put("srad", " and ((select count(tt.id) from ${tableName} tt where tt.id2= t1.id2 and tt.is_do = 4) > 0 or t1.deduction_amount>0)");
        } else if ("0".equals(settleCheck) && "0".equals(refundCheck) && "1".equals(arrearsCheck) && "1".equals(deductionCheck)) {
            sqlMap.put("srad", "and (t1.arrears>0 or t1.deduction_amount>0)");
        } else if ("0".equals(settleCheck) && "0".equals(refundCheck) && "1".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and t1.deduction_amount>0");
        } else if ("0".equals(settleCheck) && "0".equals(refundCheck) && "0".equals(arrearsCheck) && "0".equals(deductionCheck)) {
            sqlMap.put("srad", "and 1=2");
        }
        resultMap = service.selectDealInfoByCheck(sqlMap);
        return resultMap;
    }

    /**
     * 跳转到修改预约客户
     */
    @RequestMapping("/doctor_addTasks/{ids}")
    public String doctor_addTasks(@PathVariable String ids, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String sql = "select left(t.start_time,19) as startTime," +
                "( " +
                " CASE t.customer_sex " +
                " WHEN 1 THEN " +
                " '男' " +
                " WHEN 2 THEN " +
                " '女' " +
                " ELSE " +
                " '未知' " +
                " END " +
                " ) as sex ,t.* " +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t " +
                " where (arrival_status=1 or arrival_status=6 or arrival_status=7 " +
                " or arrival_status=9 or arrival_status=10) " +
                " and t.id='" + ids + "' order by arrival_time desc limit 0,1;";
        ConsultantInfo consultantInfo = sqlMapper.selectOne(sql, ConsultantInfo.class);
        if (ShiroKit.getUser().getDepartmentList() == null) {
            String getListSql = "select a.department as department from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_grade_info", 0) + " a where a.is_upper=1 group by a.department order by a.id;";
            List<DepartmentInfo> departmentList = sqlMapper.selectList(getListSql, DepartmentInfo.class);
            ShiroKit.getUser().setDepartmentList(departmentList);
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='" + tableHead + "' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList = sqlMapper.selectList(getListSql, TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        if (redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0) == null) {
            String doctorListSql = " select a.id as id,a.account as doctor from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on find_in_set(c.id,a.roleid) > 0 " +
                    " where b.abbreviation='" + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "" + "' and (c.name='医生' or c.name='洁牙师') and a.status='1';";
            List<DoctorInfo> doctorInfoList = sqlMapper.selectList(doctorListSql, DoctorInfo.class);
            redisTemplate.opsForList().leftPush(ShiroKit.getUser().getAccount() + "DoctorInfoList", doctorInfoList);
        }
        model.addAttribute("doctorList", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "DoctorInfoList", 0));
        if (StringUtil.isNotEmpty(consultantInfo.getAppointmentPayService())) {
            String payService = consultantInfo.getAppointmentPayService().substring(0, consultantInfo.getAppointmentPayService().length() - 1);
            model.addAttribute("payService", payService);
        } else {
            model.addAttribute("payService", "");
        }
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("departmentList", ShiroKit.getUser().getDepartmentList());
        model.addAttribute("consultantInfo", consultantInfo);
        model.addAttribute("customerId", ids);
        return PREFIX + "doctor_addTasks.html";
    }

    /**
     * 执行补欠款操作
     *
     * @param inputNum
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/fillOwe", method = RequestMethod.POST)
    @ResponseBody
    public int fillOwe(String inputNum) throws Exception {
        String payServiceName[] = inputNum.split("#");
        int count = payServiceName.length;
        String fillMoney = "";
        String getId = "";
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String time = DateTimeKit.now();
        for (int i = 0; i < count; i++) {
            fillMoney = inputNum.split("#")[i].split(",")[0];
            getId = inputNum.split("#")[i].split(",")[1];
            String id = CommUtils.getUUID().substring(0, 10) + DateTimeKit.now();
            String insertSql = "insert into " + tableName1 + " " +
                    "(id,customer_id,reservations_id,rid,pay_service_id,tc_name,pay_service_num,pay_service_numOld,service_actual_pay," +
                    "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,left_up," +
                    "left_down,right_up,right_down,doctor,nurse,id2,is_do,arrears)  " +
                    "select '" + id + "',customer_id,reservations_id,rid,pay_service_id,tc_name,pay_service_num,pay_service_numOld," +
                    fillMoney + ",remarks,'已开单', operation_time  ,'" + time + "',outpatient_name," +
                    "folding_pay,pay_service,left_up, left_down,right_up,right_down,doctor,nurse,id2,3,'-" + fillMoney + "'  from " + tableName1 + " " +
                    "where id='" + getId + "'";
            sqlMapper.insert(insertSql);
        }
        return 1;
    }

    /**
     * 执行退款操作
     *
     * @param parpams
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/returnMonrys", method = RequestMethod.POST)
    @ResponseBody
    public int returnMonrys(String parpams) throws Exception {
        String payServiceName[] = parpams.split("#");
        int count = payServiceName.length;
        String fillMoney = "";
        String getId = "";
        String dealtype = "";  //退款类型
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String time = DateTimeKit.now();
        for (int i = 0; i < count; i++) {
            fillMoney = parpams.split("#")[i].split(",")[0];
            getId = parpams.split("#")[i].split(",")[1];
            dealtype = parpams.split("#")[i].split(",")[2];
            String id = CommUtils.getUUID().substring(0, 10) + DateTimeKit.now();
            String sql = "select * from " + tableName1 + " where id='" + getId + "'";
            RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
            String insertSql = "insert into " + tableName1 + " " +
                    "(id,customer_id,reservations_id,rid,pay_service_id,tc_name,pay_service_num,pay_service_numOld,service_actual_pay," +
                    "remarks,deal_status,operation_time,get_time,outpatient_name,folding_pay,pay_service,left_up," +
                    "left_down,right_up,right_down,doctor,nurse,id2,arrears,is_do)  " +
                    "select '" + id + "',customer_id,reservations_id,rid,pay_service_id,tc_name,pay_service_num,pay_service_numOld," +
                    "-" + fillMoney + ",remarks,'已开单', operation_time  ,'" + time + "',outpatient_name," +
                    "folding_pay,pay_service ,left_up, left_down,right_up,right_down,doctor,nurse,id2, ";
            if (dealtype.equals("1")) {  //不做了
                insertSql += "0,1";
            } else if (dealtype.equals("2")) {  //继续做
                insertSql += "" + fillMoney + ",2";
            } else if (dealtype.equals("3")) {  //退款
                String sql2 = "select sum(arrears) as arrears from " + tableName1 + " where id2='" + rechargeInfo.getId2() + "'";
                RechargeInfo rechargeInfo2 = sqlMapper.selectOne(sql2, RechargeInfo.class);
                if ("0.00".equals(rechargeInfo2.getArrears())) {
                    insertSql += "" + rechargeInfo2.getArrears() + ",4";
                } else {
                    insertSql += "-" + rechargeInfo2.getArrears() + ",4";
                }
            }
            insertSql += " from " + tableName1 + " where id='" + getId + "'";
            sqlMapper.insert(insertSql);
        }
        return 1;
    }

    @RequestMapping(value = "/getDiagnosisContent", method = RequestMethod.POST)
    @ResponseBody
    public ConsultantInfo getDiagnosisContent(String diagnosisTime, String reservationsId) {
        ConsultantInfo info = new ConsultantInfo();
        if (diagnosisTime.length() > 0) {
            diagnosisTime = diagnosisTime.substring(0, 10) + " " + diagnosisTime.substring(10, 18);
            String sql = "select id as id ,reservations_id as reservationsId,customer_id as customerId,diagnosis_content as diagnosisContent," +
                    "tc_name as tcName from " +
                    "tc_diagnosis_info where reservations_id='" + reservationsId + "'and diagnosis_time='" + diagnosisTime + "'";
            info = sqlMapper.selectOne(sql, ConsultantInfo.class);
        }
        return info;
    }

    @RequestMapping(value = "/getVisitContent", method = RequestMethod.POST)
    @ResponseBody
    public CustomerInfo getVisitContent(String startTime, String reservationsId) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName = tableHead + "customer_info";
        startTime = startTime.substring(0, 10) + " " + startTime.substring(10, startTime.length());
        String sql = "select id as id ,reservations_id as reservationsId,field_tc as fieldTc,customer_status as customerStatus,doctor as doctor,appointment_pay_service as appointmentPayService, " +
                "remarks as remarks,archiving as archiving,left(create_appointment_time,19) as createAppointmentTime from " + tableName + " where reservations_id='" + reservationsId + "'and start_time='" + startTime + "'";
        CustomerInfo info = sqlMapper.selectOne(sql, CustomerInfo.class);
        return info;
    }

    @RequestMapping(value = "/getDealContent", method = RequestMethod.POST)
    @ResponseBody
    public DealInfo getDealContent(String operationTime, String reservationsId) {
        String sql = "select id as id ,is_deal as isDeal,tc_name as tcName ,remarks as remarks," +
                "no_deal_cause as noDealCause ,outpatient_name as outpatientName" +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " where reservations_id='" + reservationsId + "'and operation_time='" + operationTime + "'";
        DealInfo info = sqlMapper.selectOne(sql, DealInfo.class);
        return info;
    }

    @RequestMapping(value = "/getOpenBillList", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenBillList(String operationTime, String reservationsId) {
        if (StringUtil.isNotEmpty(operationTime) && operationTime.length() == 18) {//yyyy-mm-ddHH:mm:ss --- yyyy-mm-dd HH:mm:ss
            operationTime = operationTime.substring(0, 10) + " " + operationTime.substring(10);//中间加入空格
        }
        String sql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(sql, String.class);
        String dealTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            String s = "select id,area_abbreviation from dept where abbreviation='" + abbreviationList.get(i) + "'";
            Dept dept = sqlMapper.selectOne(s, Dept.class);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select a.*,t2.id AS payServiceId, t2.department AS department,t2.first_level_project AS firstLevelProject," +
                        " t2.pay_service_name AS payServiceName, t2.pay_service_price_one AS payServicePriceOne," +
                        " t2.pay_service_price_two AS payServicePriceTwo,t2.unit as unit," +
                        " t3.customer_name AS customerName,t3.medical_record_number AS medicalRecordNumber,t3.customer_phone AS customerPhone," +
                        " t3.customer_age AS customerAge," +
                        " (CASE t3.customer_sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END " +
                        " ) as sex " +
                        " from " + head + "deal_info a left join " + dept.getAreaAbbreviation() + "pay_service_info t2 on a.pay_service_id=t2.id " +
                        " left join " + head + "customer_info t3 on a.customer_id=t3.id " +
                        " where a.reservations_id='" + reservationsId + "'";
            } else {
                dealTables += "select a.*,t2.id AS payServiceId, t2.department AS department,t2.first_level_project AS firstLevelProject," +
                        " t2.pay_service_name AS payServiceName, t2.pay_service_price_one AS payServicePriceOne," +
                        " t2.pay_service_price_two AS payServicePriceTwo,t2.unit as unit," +
                        " t3.customer_name AS customerName,t3.medical_record_number AS medicalRecordNumber,t3.customer_phone AS customerPhone," +
                        " t3.customer_age AS customerAge," +
                        " (CASE t3.customer_sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END " +
                        " ) as sex " +
                        " from " + head + "deal_info a left join " + dept.getAreaAbbreviation() + "pay_service_info t2 on a.pay_service_id=t2.id " +
                        " left join " + head + "customer_info t3 on a.customer_id=t3.id " +
                        " where a.reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql3 = "SELECT SUM(service_actual_pay) as serviceActualPay,sum(deposit_amount) as depositAmount," +
                " sum(arrears) as  accountArrears, " +
                " t1.id AS id,t1.customer_id AS customerId,t1.reservations_id AS reservationsId,LEFT(t1.get_time,19) AS getTime,t1.outpatient_name AS outpatientName, " +
                " t1.deal_status AS dealStatus, t1.tc_name AS tcName,t1.pay_service_num AS payServiceNum,t1.pay_service_numold AS payServiceNumOld," +
                " t1.whole_discount as wholeDiscount,t1.discount_hide as discountHide," +
                " (case when t1.is_do=3 then '还欠款' when t1.is_do=4 then '退款' when t1.is_do=5 then '消费' when t1.is_do=6 then '作废' end ) isDoName,t1.primary_amount as primaryAmount," +
                " LEFT(t1.operation_time,19) AS operationTime,t1.remarks AS remarks, t1.folding_pay AS foldingPay,t1.doctor AS doctor,t1.nurse AS nurse," +
                " t1.left_up AS leftUp,t1.left_down AS leftDown,t1.right_up AS rightUp,t1.right_down AS rightDown,t1.pay_service AS payService,t1.payServiceId," +
                " t1.department,t1.firstLevelProject,t1.payServiceName,t1.payServicePriceOne, " +
                " t1.payServicePriceTwo,t1.unit,t1.customerName," +
                " t1.medicalRecordNumber,t1.customerPhone,t1.customerAge, " +
                " t1.sex,t1.tc_name AS fieldTc,t1.order_discount as orderDiscount,t1.deduction_type as deductionType,t1.id2," +
                " t1.deduction_amount as deductionAmount,t1.deduction_typeName as deductionTypeName,t1.check_status AS checkStatus  " +
                " from (" + dealTables + ") t1 " +
                " where t1.get_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "' " +
                " and (t1.deal_status in ('已结账','待审核','审核未通过') or (t1.deal_status in ('已开单','待审核','审核未通过')))" +
                " GROUP BY t1.id2,t1.get_time  order by t1.get_time desc ";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);
        return openBillList3;
    }

    @RequestMapping(value = "/getOpenBillListjz", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenBillListjz(String operationTime, String getTime, String reservationsId) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        if (StringUtil.isNotEmpty(operationTime) && operationTime.length() == 18) {//yyyy-mm-ddHH:mm:ss --- yyyy-mm-dd HH:mm:ss
            operationTime = operationTime.substring(0, 10) + " " + operationTime.substring(10);//中间加入空格
        }
        String sql3 = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , " +
                " '已结账'  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ,t1.discount_hide as discountHide," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.doctor as doctor ,t1.left_up as leftUp ,t1.left_down as leftDown ," +
                " t1.right_up as rightUp ,t1.right_down as rightDown ,t1.pay_service as payService,t1.is_do as isdo," +
                " t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                " t2.pay_service_name as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                " t3.customer_name as customerName ," +
                " t3.medical_record_number as medicalRecordNumber,t3.customer_phone as customerPhone,t3.customer_age as customerAge," +
                " t3.treatment_unit as treatmentUnit,t1.doctor as doctor,t2.unit as unit,t1.order_discount as orderDiscount,t1.whole_discount as wholeDiscount," +
                " t1.deduction_amount as deductionAmount ,t1.deduction_type as deductionType,t1.deposit_amount as depositAmount," +
                " t1.deduction_typeName as deductionTypeName," +
                "( " +
                " CASE t3.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex ,t3.arrival_store as arrivalStore,t1.tc_name as fieldTc from " + tableName1 + " t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t3 on t1.customer_id=t3.id" +
                " where t1.operation_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "' and  t1.get_time='" + getTime + "'";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);//查询当前开单除去不做了的记录 and t1.deal_status='已结账'
        Double totleReceivable = 0.00;//总的项目总价，非折后
        Double totleServiceActualPays = 0.00;//总的实付金额（除去不做的）
        Double totleFoldingPay = 0.00;//总的折后价
        Double totleDepositAmount = 0.00;//总的储值抵扣
        Double totleDeductionAmount = 0.00;//总的优惠抵扣
        Double accountArrears = 0.00;
        for (int i = 0; i < openBillList3.size(); i++) {
            PayServicedInfo info = openBillList3.get(i);
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        BigDecimal bd1 = new BigDecimal(Double.toString(info.getPayServicePriceTwo()));
                        BigDecimal bd2 = new BigDecimal(Double.toString(info.getPayServiceNum()));
                        Double receivable = bd1.multiply(bd2).doubleValue();
                        totleReceivable = BigDecimal.valueOf(totleReceivable).add(BigDecimal.valueOf(receivable)).doubleValue();
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay = BigDecimal.valueOf(totleFoldingPay).add(BigDecimal.valueOf(info.getFoldingPay())).doubleValue();
                    }
                    if (info.getArrears() != null) {
                        accountArrears = BigDecimal.valueOf(accountArrears).add(BigDecimal.valueOf(info.getArrears())).doubleValue();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPays = BigDecimal.valueOf(totleServiceActualPays).add(BigDecimal.valueOf(info.getServiceActualPay())).doubleValue();
                    }
                    if (info.getDepositAmount() != null) {
                        totleDepositAmount = BigDecimal.valueOf(totleDepositAmount).add(BigDecimal.valueOf(info.getDepositAmount())).doubleValue();
                    }
                    if (info.getDeductionAmount() != null) {
                        totleDeductionAmount = BigDecimal.valueOf(totleDeductionAmount).add(BigDecimal.valueOf(info.getDeductionAmount())).doubleValue();
                    }
                    info.setTotleDeductionAmount(totleDeductionAmount);
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleDepositAmount(totleDepositAmount);
                    info.setTotleServiceActualPay(totleServiceActualPays);
                    info.setAccountArrears(accountArrears);
                }
            }
        }
        return openBillList3;
    }

    //到店成交记录
    @RequestMapping(value = "/getOpenBillLisChengjiao", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenBillLisChengjiao(String operationTime, String getTime, String customerId, String
            reservationsId, String filterMoney) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String tableName4 = tableHead + "recharge_info";
        if (StringUtil.isNotEmpty(operationTime) && operationTime.length() == 18) {//yyyy-mm-ddHH:mm:ss --- yyyy-mm-dd HH:mm:ss
            operationTime = operationTime.substring(0, 10) + " " + operationTime.substring(10);//中间加入空格
        }
        String sql3 = "SELECT t1.outpatient_name as outpatientName,(case  " +
                "  when t1.is_do=3 then '还欠款' " +
                "  when t1.is_do=4 then '退款' " +
                "  when t1.is_do=5 then '消费' " +
                "  when t1.is_do=6 then '作废' " +
                "  end " +
                "  ) isDoName,t2.pay_service_name as payServiceName,t4.actual_account_balance as actualAccountBalance,t1.operation_time as operationTime , " +
                "  t4.recharge_time as rechargeTime,t1.deal_status as dealStatus,t4.recharge_people as rechargePeople,t1.remarks as remarks,t4.remarks as remarkss," +
                " t1.arrears as arrears,t4.id as rechargeId,t1.doctor as doctor,t4.checkout_method as checkoutMethod from " + tableName1 + " t1 " +
                " left join " + tableName4 + " t4  on t4.deal_id=t1.id  " +
                " left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id " +
                " where t1.operation_time='" + operationTime + "'  and t1.reservations_id='" + reservationsId + "' " +
                " and t1.deal_status='已结账' and t4.id is not null  order by t4.recharge_time desc  ";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);
        return openBillList3;
    }

    //市场成交记录
    @RequestMapping(value = "/getOpenBillLisChengjiaoSC", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenBillLisChengjiaoSC(String operationTime, String getTime, String customerId, String
            reservationsId, String filterMoney) {
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);  //到过的店
        String dealTables = "";
        String rechargeTables = "";
        String tableNames = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
                tableNames += "select * from " + head + "customer_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
                tableNames += "select * from " + head + "customer_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        operationTime = operationTime.substring(0, 19);
        String sql3 = "SELECT  service_actual_pay  as serviceActualPay, arrears as  arrears, " +
                " t1.id AS id,t1.customer_id AS customerId,t1.reservations_id AS reservationsId,left(t1.get_time,19) AS getTime,t1.outpatient_name AS outpatientName, " +
                " t1.deal_status AS dealStatus, t1.tc_name AS tcName,t1.pay_service_num AS payServiceNum,t4.recharge_people as rechargePeople," +
                " LEFT(t1.operation_time,19) AS operationTime,t1.remarks AS remarks, t1.folding_pay AS foldingPay,t1.doctor AS doctor," +
                "(case  " +
                "  when t1.is_do=1   then '不做了' " +
                "  when t1.is_do=2   then '继续做' " +
                "  when t1.is_do=3   then '还欠款' " +
                "  when t1.is_do=4   then '退款' " +
                "  when t1.is_do=5   then '消费' " +
                "  when t1.is_do=6   then '作废' " +
                "  end " +
                "  ) isDoName," +
                " t2.department AS department,t2.first_level_project AS firstLevelProject,t2.pay_service_name AS payServiceName, t2.pay_service_price_one AS payServicePriceOne, " +
                " t2.pay_service_price_two AS payServicePriceTwo,t3.customer_name AS customerName," +
                " t3.medical_record_number AS medicalRecordNumber,t3.customer_phone AS customerPhone,t3.customer_age AS customerAge,t1.id2, " +
                "  (CASE t3.customer_sex  " +
                "  WHEN 1 THEN  " +
                "  '男'  " +
                "  WHEN 2 THEN  " +
                "  '女'  " +
                "  ELSE  " +
                "  '未知'  " +
                "  END  " +
                " ) as sex ,t3.arrival_store AS arrivalStore,t1.tc_name AS fieldTc,t1.check_status AS checkStatus,t4.checkout_method as checkoutMethod " +
                " from (" + dealTables + ") t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id left join (" + tableNames + ") t3 on t1.customer_id=t3.id" +
                " left join (" + rechargeTables + ") t4 on t4.id2=t1.id where t1.operation_time='" + operationTime + "' " +
                " and t1.reservations_id='" + reservationsId + "' " +
                " and (t1.deal_status='已结账' or t1.deal_status='已开单')" +
                " order by t1.get_time desc ";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);
        Double totleReceivable = 0.00;//总的项目总价，非折后
        Double totleServiceActualPays = 0.00;//总的实付金额（除去不做的）
        Double totleFoldingPay = 0.00;//总的折后价
        Double accountArrears = 0.00;
        for (PayServicedInfo info : openBillList3) {
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPays += info.getServiceActualPay();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPays);
                    info.setAccountArrears(accountArrears);
                }
            }
        }
        return openBillList3;
    }

    /**
     * 当前医生的成交记录
     *
     * @param operationTime
     * @param getTime
     * @param customerId
     * @param reservationsId
     * @param filterMoney
     * @return
     */
    @RequestMapping(value = "/getOpenBillLisHuajia", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenBillLisHuajia(String operationTime, String getTime, String customerId, String
            reservationsId, String filterMoney) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String tableName4 = tableHead + "recharge_info";
        if (StringUtil.isNotEmpty(operationTime) && operationTime.length() == 18) {//yyyy-mm-ddHH:mm:ss --- yyyy-mm-dd HH:mm:ss
            operationTime = operationTime.substring(0, 10) + " " + operationTime.substring(10);//中间加入空格
        }
        String sql3 = "SELECT  service_actual_pay  as serviceActualPay, arrears as  arrears, " +
                " t1.id AS id,t1.customer_id AS customerId,t1.reservations_id AS reservationsId,left(t1.get_time,19) AS getTime,t1.outpatient_name AS outpatientName, " +
                " t1.deal_status AS dealStatus, t1.tc_name AS tcName,t1.pay_service_num AS payServiceNum," +
                " LEFT(t1.operation_time,19) AS operationTime,t1.remarks AS remarks, t1.folding_pay AS foldingPay,t1.doctor AS doctor," +
                "(case  " +
                "  when t1.is_do=1   then '不做了' " +
                "  when t1.is_do=2   then '继续做' " +
                "  when t1.is_do=3   then '还欠款' " +
                "  when t1.is_do=4   then '退款' " +
                "  when t1.is_do=5   then '消费' " +
                "  when t1.is_do=6   then '作废' " +
                "  end " +
                "  ) isDoName," +
                " t2.department AS department,t2.first_level_project AS firstLevelProject,t2.pay_service_name AS payServiceName, t2.pay_service_price_one AS payServicePriceOne, " +
                " t2.pay_service_price_two AS payServicePriceTwo,t3.customer_name AS customerName," +
                " t3.medical_record_number AS medicalRecordNumber,t3.customer_phone AS customerPhone,t3.customer_age AS customerAge,t1.id2, " +
                " ( " +
                " CASE t3.customer_sex " +
                "  WHEN 1 THEN " +
                "   '男' " +
                "  WHEN 2 THEN " +
                "   '女' " +
                "  ELSE " +
                "   '未知' " +
                "  END " +
                "  ) as sex," +
                " t3.arrival_store AS arrivalStore,t1.tc_name AS fieldTc,t1.check_status AS checkStatus,t4.checkout_method as checkoutMethod " +
                " from " + tableName1 + " t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t3 on t1.customer_id=t3.id" +
                " left join " + tableName4 + " t4 on t1.reservations_id=t4.reservations_id where t1.operation_time='" + operationTime + "' and t1.doctor='" + ShiroKit.getUser().getAccount() + "' and t1.reservations_id='" + reservationsId + "' " +
                " and (t1.deal_status='已结账' or t1.deal_status='已开单')" +
                " GROUP BY t1.id  order by t1.get_time desc ";
        List<PayServicedInfo> openBillList3 = sqlMapper.selectList(sql3, PayServicedInfo.class);
        Double totleReceivable = 0.00;//总的项目总价，非折后
        Double totleServiceActualPays = 0.00;//总的实付金额（除去不做的）
        Double totleFoldingPay = 0.00;//总的折后价
        Double accountArrears = 0.00;
        for (PayServicedInfo info : openBillList3) {
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getPayServiceNum() != null) {
                        totleReceivable += (info.getPayServicePriceTwo()) * (info.getPayServiceNum());
                    }
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPays += info.getServiceActualPay();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPays);
                    info.setAccountArrears(accountArrears);
                }
            }
        }
        return openBillList3;
    }

    /**
     * 退款请求的数据
     *
     * @param operationTime
     * @param customerId
     * @param reservationsId
     * @return
     */
    @RequestMapping(value = "/getTuiPrint", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getTuiPrint(String operationTime, String customerId,
                                             String reservationsId) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId " +
                ",t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName " +
                ",t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left (t1.operation_time,19) as operationTime,t1.remarks as remarks," +
                " t1.folding_pay as foldingPay,t1.doctor ,t1.left_Up leftUp ," +
                "t1.right_Up as rightUp,t1.left_Down as leftDown,t1.right_Down as rightDown,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "t2.pay_service_name as payServiceName, t2.pay_service_price_one as  payServicePriceOne, " +
                "t2.pay_service_price_two as payServicePriceTwo," +
                "t1.type_number as typeNumber,t3.customer_name as customerName ,t3.medical_record_number as" +
                " medicalRecordNumber,t3.customer_phone as customerPhone,t3.customer_age as customerAge, " +
                "( " +
                " CASE t3.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex ,t3.arrival_store  as " +
                "arrivalStore,t3.field_tc as fieldTc from " + tableName1 + " t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2" +
                " on t1.pay_service_id=t2.id left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t3 on t1.customer_id=t3.id where t1.operation_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "'";
        List<PayServicedInfo> openPrintList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return openPrintList;
    }

    @RequestMapping(value = "/getNoDealCause", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getNoDealCause(String operationTime, String reservationsId) {
        String sql = "select t.id as id , t.reservations_id as reservationsId ,t.customer_id as customerId ,t.outpatient_name " +
                "as outpatientName ,t.tc_name as tcName,t.pay_service_id as payServiceId,t.no_deal_cause as noDealCause,t.operation_time as operationTime," +
                "t1.department as department,t1.first_level_project as firstLevelProject" +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 on t.pay_service_id=t1.id " +
                " where t.operation_time='" + operationTime + "' and  t.reservations_id='" + reservationsId + "'";
        List<PayServicedInfo> openBillList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return openBillList;
    }

    @RequestMapping(value = "/to_print/{id}")
    public String print(@PathVariable String id, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser
                    ().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(id)) {
            return "/errorClose.html";
        }
        try {
            String sql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " where id='" + id + "' limit 1";
            RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
            String sql1 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                    "  (CASE t2.customer_sex  " +
                    "  WHEN 1 THEN  " +
                    "  '男'  " +
                    "  WHEN 2 THEN  " +
                    "  '女'  " +
                    "  ELSE  " +
                    "  '未知'  " +
                    "  END  " +
                    " ) as sex ,t2.arrival_store as outpatientName,t2.customer_age as " +
                    "customerAge,t2.customer_phone as customerPhone ,t2.field_tc as tcName" +
                    " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t2 where id='" +
                    rechargeInfo.getCustomerId() + "'";
            ConsultantInfo consultantInfo = sqlMapper.selectOne(sql1, ConsultantInfo.class);
            String sql22 = "select id,simplename,address,phone from dept where abbreviation='" + tableHead + "'";
            Dept dept = sqlMapper.selectOne(sql22, Dept.class);
            String sql2 = "select GROUP_CONCAT(distinct doctor) as doctor from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t1 " +
                    "where t1.get_time='" + rechargeInfo.getGetTime() + "' " +
                    "and t1.reservations_id='" + rechargeInfo.getReservationsId() + "'";
            PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql2, PayServiceInfo.class);
            model.addAttribute("doctor", payServiceInfo.getDoctor());
            model.addAttribute("deptInfo", dept);
            model.addAttribute("consultantInfo", consultantInfo);
            model.addAttribute("rechargeInfo", rechargeInfo);
            model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
            model.addAttribute("customerId", rechargeInfo.getCustomerId());
            model.addAttribute("getTime", rechargeInfo.getGetTime());
            model.addAttribute("operationTime", rechargeInfo.getOperationTime());
            model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("abbreviation", tableHead);
            String tableName1 = tableHead + "recharge_info";
            String isEarnes = "";
            if (("1").equals(rechargeInfo.getIsDo()) || ("2").equals(rechargeInfo.getIsDo()) || ("4").equals(rechargeInfo.getIsDo())) {  //退款
                isEarnes = "";
            } else {
                isEarnes = "or isEarnes=5";
            }
            String sql4 = "select sum(actual_account_balance) as actualAccountBalance,a.checkout_method as  checkoutMethod from " + tableName1 + " a" +
                    " where reservations_id='" + rechargeInfo.getReservationsId()
                    + "' and operation_time='" + rechargeInfo.getGetTime() + "' " +
                    " and (isEarnes=1 " + isEarnes + ") and actual_account_balance !=0 group by checkout_method ;";
            List<RechargeInfo> openCheckoutMethodInfoList = sqlMapper.selectList(sql4, RechargeInfo.class);
            model.addAttribute("openCheckoutMethodInfoList", openCheckoutMethodInfoList);
            String sql5 = "select id, GROUP_CONCAT(distinct remarks) as remarks,recharge_time as rechargeTime,recharge_people as rechargePeople from " + tableName1 + " a" +
                    " where reservations_id='" + rechargeInfo.getReservationsId()
                    + "' and operation_time='" + rechargeInfo.getGetTime() + "' and isEarnes=1";
            RechargeInfo remarksList = sqlMapper.selectOne(sql5, RechargeInfo.class);
            if (remarksList != null) {
                model.addAttribute("remarksList", remarksList);
                model.addAttribute("rechargeTime", remarksList.getRechargeTime());
                model.addAttribute("rechargePeople", remarksList.getRechargePeople());
            }
            if ("yiya_wh_we_".equals(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "")) {
                model.addAttribute("isDo", rechargeInfo.getIsDo());
                return PREFIX + "printFive.html";
            } else {
                //1:不做了,2:继续做,3:还欠款,4:退款,5:消费,6:作废
                if (("1").equals(rechargeInfo.getIsDo()) || ("2").equals(rechargeInfo.getIsDo()) || ("4").equals(rechargeInfo.getIsDo())) {  //退款
                    return PREFIX + "printThr.html";
                } else if (("3").equals(rechargeInfo.getIsDo())) {  //还欠款
                    return PREFIX + "printTwo.html";
                } else {  //结账
                    return PREFIX + "printOne.html";
                }
            }
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    @RequestMapping(value = "/to_prints/{operationTime}/{reservationsId}/{outpatientName}/{getTime}")
    public String to_prints(@PathVariable String operationTime, @PathVariable String reservationsId,
                            @PathVariable String outpatientName, @PathVariable String getTime, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser
                    ().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        try {
            String abbreviationSql = "select id,abbreviation,simplename,address,phone from dept t where t.simplename='" + outpatientName + "'";
            Dept dept = sqlMapper.selectOne(abbreviationSql, Dept.class);
            String tableName = dept.getAbbreviation() + "deal_info";
            String tableNamer = dept.getAbbreviation() + "recharge_info";
            String sql = "select * from " + tableName + " where reservations_id='" + reservationsId + "' and get_time='" + getTime + "'" +
                    "and operation_time='" + operationTime + "' limit 1";
            RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
            String sql1 = "select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                    "  (CASE t2.customer_sex  " +
                    "  WHEN 1 THEN  " +
                    "  '男'  " +
                    "  WHEN 2 THEN  " +
                    "  '女'  " +
                    "  ELSE  " +
                    "  '未知'  " +
                    "  END  " +
                    " ) as sex ,t2.arrival_store as outpatientName,t2.customer_age as " +
                    "customerAge,t2.customer_phone as customerPhone ,t2.field_tc as tcName " +
                    "from " + dept.getAbbreviation() + "customer_info t2 where id='" + rechargeInfo.getCustomerId() + "'";
            ConsultantInfo consultantInfo = sqlMapper.selectOne(sql1, ConsultantInfo.class);
            String sql2 = "select GROUP_CONCAT(distinct doctor) as doctor from " + tableName + " t1 " +
                    "where t1.get_time='" + rechargeInfo.getGetTime() + "' " +
                    "and t1.reservations_id='" + rechargeInfo.getReservationsId() + "'";
            PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql2, PayServiceInfo.class);
            model.addAttribute("doctor", payServiceInfo.getDoctor());
            model.addAttribute("deptInfo", dept);
            model.addAttribute("consultantInfo", consultantInfo);
            model.addAttribute("rechargeInfo", rechargeInfo);
            model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
            model.addAttribute("customerId", rechargeInfo.getCustomerId());
            model.addAttribute("getTime", rechargeInfo.getGetTime());
            model.addAttribute("operationTime", rechargeInfo.getOperationTime());
            model.addAttribute("reservationsId", rechargeInfo.getReservationsId());
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("abbreviation", dept.getAbbreviation());
            String sql4 = "select sum(actual_account_balance) as actualAccountBalance,a.checkout_method as  checkoutMethod from " + tableNamer + " a" +
                    " where reservations_id='" + rechargeInfo.getReservationsId()
                    + "' and operation_time='" + rechargeInfo.getGetTime() + "' " +
                    " and (isEarnes=1 or isEarnes=5) and actual_account_balance !=0 group by checkout_method ;";
            List<RechargeInfo> openCheckoutMethodInfoList = sqlMapper.selectList(sql4, RechargeInfo.class);
            model.addAttribute("openCheckoutMethodInfoList", openCheckoutMethodInfoList);
            String sql5 = "select id, GROUP_CONCAT(distinct remarks) as remarks,recharge_time as rechargeTime,recharge_people as rechargePeople from " + tableNamer + " a" +
                    " where reservations_id='" + rechargeInfo.getReservationsId()
                    + "' and operation_time='" + rechargeInfo.getGetTime() + "' and isEarnes=1";
            RechargeInfo remarksList = sqlMapper.selectOne(sql5, RechargeInfo.class);
            if (remarksList != null) {
                model.addAttribute("remarksList", remarksList);
                model.addAttribute("rechargeTime", remarksList.getRechargeTime());
                model.addAttribute("rechargePeople", remarksList.getRechargePeople());
            }
            if ("yiya_wh_we_".equals(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "")) {
                model.addAttribute("isDo", rechargeInfo.getIsDo());
                return PREFIX + "printFive.html";
            } else {
                //1:不做了,2:继续做,3:还欠款,4:退款,5:消费,6:作废
                if (("1").equals(rechargeInfo.getIsDo()) || ("2").equals(rechargeInfo.getIsDo()) || ("4").equals(rechargeInfo.getIsDo())) {  //退款
                    return PREFIX + "printThr.html";
                } else if (("3").equals(rechargeInfo.getIsDo())) {  //还欠款
                    return PREFIX + "printTwo.html";
                } else {  //结账
                    return PREFIX + "printOne.html";
                }
            }
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    @RequestMapping(value = "/to_printP/{operationTime}/{reservationsId}/{outpatientName}")
    public String printP(@PathVariable(value = "operationTime") String operationTime,
                         @PathVariable(value = "reservationsId") String reservationsId, @PathVariable(value = "outpatientName") String outpatientName, Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser
                    ().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            return "/errorClose.html";
        }
        if (ToolUtil.isEmpty(operationTime)) {
            return "/errorClose.html";
        }
        if (ToolUtil.isEmpty(outpatientName)) {
            return "/errorClose.html";
        }
        try {
            String abbreviationSql = "select id,abbreviation,simplename,address,phone from dept t where t.simplename='" + outpatientName + "'";
            Dept dept = sqlMapper.selectOne(abbreviationSql, Dept.class);
            String tableName = dept.getAbbreviation() + "deal_info";
            String tableNamer = dept.getAbbreviation() + "recharge_info";
            String sql = "select * from " + tableName + " where reservations_id='" + reservationsId + "' and operation_time='" + operationTime + "' order by get_time desc limit 1 ";
            RechargeInfo rechargeInfo = sqlMapper.selectOne(sql, RechargeInfo.class);
            String sql1 = " select t2.customer_name as customerName,t2.reservations_id as reservationsId,t2.medical_record_number as medicalRecordNumber," +
                    "  (CASE t2.customer_sex  " +
                    "  WHEN 1 THEN  " +
                    "  '男'  " +
                    "  WHEN 2 THEN  " +
                    "  '女'  " +
                    "  ELSE  " +
                    "  '未知'  " +
                    "  END  " +
                    " ) as sex ,t2.arrival_store as outpatientName,t2.customer_age as " +
                    " customerAge,t2.customer_phone as customerPhone ,t2.field_tc as tcName " +
                    " from " + dept.getAbbreviation() + "customer_info t2 where t2.reservations_id='" + reservationsId + "' limit 0,1";
            ConsultantInfo consultantInfo = sqlMapper.selectOne(sql1, ConsultantInfo.class);
            String sql2 = "select GROUP_CONCAT(distinct doctor) as doctor,operation_time as operationTime from " + tableName + " t1 " +
                    "where t1.get_time='" + operationTime + "' " +
                    "and t1.reservations_id='" + reservationsId + "'";
            PayServiceInfo payServiceInfo = sqlMapper.selectOne(sql2, PayServiceInfo.class);
            model.addAttribute("doctor", payServiceInfo.getDoctor());
            model.addAttribute("deptInfo", dept);
            model.addAttribute("consultantInfo", consultantInfo);
            model.addAttribute("rechargeInfo", rechargeInfo);
            model.addAttribute("reservationsId", reservationsId);
            model.addAttribute("getTime", rechargeInfo.getGetTime());
            model.addAttribute("operationTime", operationTime);
            model.addAttribute("userName", ShiroKit.getUser().getAccount());
            model.addAttribute("abbreviation", dept.getAbbreviation());
            String sql4 = "select sum(actual_account_balance) as actualAccountBalance,a.checkout_method as checkoutMethod," +
                    " GROUP_CONCAT(distinct document_number) as documentNumber from " + tableNamer + " a" +
                    " where reservations_id='" + reservationsId + "' " +
                    " and operation_time in (select get_time from " + tableName + " where operation_time='" + payServiceInfo.getOperationTime() + "') " +
                    " and actual_account_balance !=0 group by checkout_method ;";
            List<RechargeInfo> openCheckoutMethodInfoList = sqlMapper.selectList(sql4, RechargeInfo.class);
            model.addAttribute("openCheckoutMethodInfoList", openCheckoutMethodInfoList);
            String sql5 = "select id, GROUP_CONCAT(distinct remarks) as remarks,recharge_time as rechargeTime,recharge_people as rechargePeople from " + tableNamer + " a" +
                    " where reservations_id='" + rechargeInfo.getReservationsId()
                    + "' and operation_time='" + operationTime + "' and isEarnes=1";
            RechargeInfo remarksList = sqlMapper.selectOne(sql5, RechargeInfo.class);
            if (remarksList != null) {
                model.addAttribute("remarksList", remarksList);
                model.addAttribute("rechargeTime", remarksList.getRechargeTime());
                model.addAttribute("rechargePeople", remarksList.getRechargePeople());
            }
            return PREFIX + "printFour.html";
        } catch (Exception e) {
            return "/errorClose.html";
        }
    }

    //消费 、还欠款打印
    @RequestMapping(value = "/getOpenPrint", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenPrint(String operationTime, String getTime, String abbreviation, String reservationsId) {
        String tableName1 = abbreviation + "deal_info";
        String tableName3 = abbreviation + "customer_info";
        String sql22 = "select * from dept where abbreviation='" + abbreviation + "'";
        Dept dept = sqlMapper.selectOne(sql22, Dept.class);
        String tableNamep = dept.getAreaAbbreviation() + "pay_service_info";
        String sql = " select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName ," +
                " t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum , sum(t1.service_actual_pay) as serviceActualPay ," +
                " left (t1.operation_time,19) as operationTime,t1.remarks as remarks,sum(t1.deposit_amount) as depositAmount, sum(t1.deduction_amount) as deductionAmount," +
                " if((select sum(folding_pay) from " + tableName1 + " where operation_time=t1.operation_time and operation_time=get_time and id2=t1.id2) is" +
                " null,0,(select sum(folding_pay) from " + tableName1 + " where operation_time=t1.operation_time and operation_time=get_time and id2=t1.id2)) as foldingPay ,t1.doctor ,t1.left_Up leftUp ,t1.right_Up as rightUp,t1.left_Down as leftDown," +
                " t1.right_Down as rightDown,t1.pay_service as payService,t2.id as payServiceId,t2.department as department,t2.unit as unit," +
                " t2.first_level_project as firstLevelProject,sum(t1.arrears) as arrears,t2.pay_service_name as payServiceName,t2.pay_service_price_one as payServicePriceOne," +
                " t2.pay_service_price_two as payServicePriceTwo,t1.type_number as typeNumber," +
                " t3.customer_name as customerName ,t3.medical_record_number as medicalRecordNumber,t3.customer_phone as customerPhone,t3.customer_age as customerAge," +
                "(" +
                " CASE t3.customer_sex " +
                " WHEN 1 THEN " +
                "     '男' " +
                " WHEN 2 THEN " +
                "     '女' " +
                " ELSE " +
                "     '未知' " +
                " END " +
                "    ) as sex,t1.whole_discount as wholeDiscount ,t1.order_discount as orderDiscount ,t3.arrival_store as arrivalStore,(t1.pay_service_num*t1.pay_service) as primaryAmount,t3.field_tc as fieldTc," +
                "IFNULL((select sum(arrears) from " + tableName1 + " b " +
                " where b.reservations_id='" + reservationsId + "' and b.operation_time='" + operationTime + "' and b.deal_status ='已结账' and b.get_time<'" + getTime + "' ),0.00) " +
                " as scarrears, " +   //上次欠款
                "IFNULL((select sum(arrears) from " + tableName1 + " b " +
                " where b.reservations_id='" + reservationsId + "' and b.operation_time='" + operationTime + "' and b.deal_status ='已结账' ),0.00) " +
                " as sumarrears " +   //本单共欠款
                " from " + tableName1 + " t1 left join " + tableNamep + " t2 " +
                " on t1.pay_service_id=t2.id left join " + tableName3 + " t3 on t1.customer_id=t3.id where 1=1 " +
                " and t1.operation_time='" + operationTime + "' and t1.get_time='" + getTime + "' and t1.reservations_id='" + reservationsId + "' group by t1.id2";
        String tableName2 = abbreviation + "recharge_info";
        String sql2 = "select document_number from " + tableName2 + " where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + getTime + "' and isEarnes!=9 and isEarnes!=7 and deal_id is not null limit 1;";
        PayServiceInfo openPrintList1 = sqlMapper.selectOne(sql2, PayServiceInfo.class);
        List<PayServicedInfo> openPrintList = sqlMapper.selectList(sql, PayServicedInfo.class);
        openPrintList.get(0).setDocumentNumber(openPrintList1.getDocumentNumber());
        return openPrintList;
    }


    //退款打印列表
    @RequestMapping(value = "/getOpenPrintTK", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getOpenPrintTK(String operationTime, String getTime, String abbreviation, String reservationsId) {
        String tableName1 = abbreviation + "deal_info";
        String sql22 = "select * from dept where abbreviation='" + abbreviation + "'";
        Dept dept = sqlMapper.selectOne(sql22, Dept.class);
        String tableNamep = dept.getAreaAbbreviation() + "pay_service_info";
        String sql = "select  t2.pay_service_name as payServiceName ,t1.service_actual_pay as serviceActualPay," +
                "t1.arrears as arrears,(t1.pay_service_num*t1.pay_service) as primaryAmount,t1.whole_discount as wholeDiscount ," +
                "(t1.service_actual_pay+t1.arrears) as totleArrears," +  //退款加欠费总额
                "(select sum(arrears) from " + tableName1 + " b where t1.reservations_id=b.reservations_id" +
                " and b.deal_status='已结账' and operation_time=t1.operation_time) as accountArrears," +  //累计欠款
                "IFNULL((select sum(arrears) from " + tableName1 + " b " +
                " where b.reservations_id='" + reservationsId + "' and b.operation_time='" + operationTime + "' and b.deal_status ='已结账' and b.get_time<'" + getTime + "' ),0.00) " +
                " as scarrears " +   //上次欠款
                " from " + tableName1 + " t1 left join " + tableNamep + " t2" +
                " on t1.pay_service_id=t2.id where 1=1" +
                " and t1.operation_time='" + operationTime + "' and t1.reservations_id='" + reservationsId + "' and t1.get_time='" + getTime + "'";
        String tableName2 = abbreviation + "recharge_info";
        String sql2 = "select document_number from " + tableName2 + " where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + getTime + "' and isEarnes!=9 and isEarnes!=7 and deal_id is not null limit 1;";
        PayServiceInfo openPrintList1 = sqlMapper.selectOne(sql2, PayServiceInfo.class);
        List<PayServicedInfo> openPrintList = sqlMapper.selectList(sql, PayServicedInfo.class);
        openPrintList.get(0).setDocumentNumber(openPrintList1.getDocumentNumber());
        return openPrintList;
    }

    @RequestMapping(value = "/getCheckoutMethod", method = RequestMethod.POST)
    @ResponseBody
    public List<RechargeInfo> getCheckoutMethod(String reservationsId, String getTime) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "recharge_info";
        String sql = "select * from " + tableName1 + " where reservations_id='" + reservationsId + "' " +
                "and operation_time='" + getTime + "' and isEarnes!=9 and isEarnes!=7 and deal_id is not null;";
        List<RechargeInfo> openCheckoutMethodInfoList = sqlMapper.selectList(sql, RechargeInfo.class);
        return openCheckoutMethodInfoList;
    }

    @RequestMapping(value = "/getCheckoutMethod1", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> getCheckoutMethod1(String reservationsId, String getTime, String operationTime) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        String tableName2 = tableHead + "recharge_info";
        String sql = "select t.id,t.service_actual_pay as serviceActualPay ,t.arrears as arrears ,t.folding_pay as foldingPay from " + tableName1 + " t " +
                " where t.reservations_id='" + reservationsId + "' " +
                " and t.operation_time='" + operationTime + "'and t.deal_status='已结账';";
        List<PayServicedInfo> openCheckoutMethodInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return openCheckoutMethodInfoList;
    }

    @RequestMapping(value = "/updateDealInfo")
    @ResponseBody
    public String updateDealInfo(@RequestBody List<PaymentInfo> list) throws Exception {
        String result = "";
        try {
            String dealStatus = service.selectDealStatus(list.get(0).getReservationsId(), list.get(0).getOperationTime());
            if ("已开单".equals(dealStatus) || "待审核".equals(dealStatus)) {
                if ((redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "RoleName", 0) + "").indexOf("前台") != -1) {  //当前登录人是前台，
                    for (int i = 0; i < list.size(); i++) {
                        PaymentInfo info = list.get(i);
                        String sql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " " +
                                " where id='" + info.getPayServiceId() + "' and is_teeth_project=1";
                        List<PayServicedInfo> plist = sqlMapper.selectList(sql1, PayServicedInfo.class);
                        if (plist.size() == 0) {  //开单项目中有不该前台开的单
                            return "55,''";
                        }
                    }
                }
                service.deleteDealNew(list.get(0).getReservationsId(), list.get(0).getOperationTime());
                result = service.insertDealNew(list);
            } else if ("已结账".equals(dealStatus)) {
                result = "-2,''";
            } else {
                result = "-3,''";
            }
        } catch (Exception e) {
            e.printStackTrace();
            result = "-1,''";
        }
        return result;
    }

    @RequestMapping(value = "/repairArrears")
    @ResponseBody
    public int repairArrears(String operationTimes, Double arrearsAmount, String reservationsId, String arrearsRemark, String tcName, String customerId, String arrearsOutpatientName) {
        return service.repairArrears(operationTimes, arrearsAmount, reservationsId, arrearsRemark, tcName, customerId, arrearsOutpatientName);
    }

    //补欠款
    @RequestMapping(value = "/repairArrearsYh")
    @ResponseBody
    public int repairArrearsYh(@RequestBody List<PaymentInfo> list, Model model) throws Exception {
        return service.repairArrearsYh(list);
    }


    @RequestMapping(value = "/openBillNew")
    @ResponseBody
    public String openBillNew(@RequestBody List<PaymentInfo> list, Model model) throws Exception {
        String customerId = list.get(0).getCustomerId();
        model.addAttribute("customerId", customerId);
        String upsql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " set deal_status='已开单' where id='" + customerId + "'" +
                " and arrival_status!=1 and arrival_status!=6 and arrival_status!=7 and arrival_status!=8 and arrival_status!=9 and arrival_status!=10 and arrival_time is not null" +
                " and customer_status!='复诊' ";
        sqlMapper.update(upsql);
        String sql2 = "select id,isdeal_status,customer_status,deal_status,arrival_status from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " t" +
                " where t.id='" + customerId + "' ";
        CustomerInfo customerInfo1 = sqlMapper.selectOne(sql2, CustomerInfo.class);
        if ("1".equals(customerInfo1.getArrivalStatus()) || "6".equals(customerInfo1.getArrivalStatus()) || "9".equals(customerInfo1.getArrivalStatus()) ||
                "7".equals(customerInfo1.getArrivalStatus()) || "8".equals(customerInfo1.getArrivalStatus()) || "10".equals(customerInfo1.getArrivalStatus())) {
            return "22,''";
        }
        try {
            String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " where id='" + customerId + "' limit 0,1";
            CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
            if (customerInfo != null) {
                if (!"已结账".equals(customerInfo.getDealStatus())) {
                    String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " set deal_status='已开单' where id='" + customerId + "'";
                    sqlMapper.update(sql);
                }
            }
            if ((redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "RoleName", 0) + "").indexOf("前台") != -1) {  //当前登录人是前台，
                for (int i = 0; i < list.size(); i++) {
                    PaymentInfo info = list.get(i);
                    String sql1 = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " " +
                            " where id='" + info.getPayServiceId() + "' and is_teeth_project=1";
                    List<PayServicedInfo> plist = sqlMapper.selectList(sql1, PayServicedInfo.class);
                    if (plist.size() == 0) {  //开单项目中有不该前台开的单
                        return "55,''";
                    }
                }
            }
            return service.insertDealNew(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "1,''";
    }


    @RequestMapping(value = "/backAllOrder")
    @ResponseBody
    public int backAllOrder(@RequestBody List<PaymentInfo> list, Model model) throws Exception {
        String customerId = list.get(0).getCustomerId();
        model.addAttribute("customerId", customerId);
        try {
            String selectsql = "select * from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " where id='" + customerId + "' limit 0,1";
            CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
            if (customerInfo != null) {
                if (!"已结账".equals(customerInfo.getDealStatus())) {
                    String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " set deal_status='已开单' where id='" + customerId + "'";
                    sqlMapper.update(sql);
                }
            }
            return service.backAllOrder(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    @RequestMapping(value = "/updateDocAchievement")
    @ResponseBody
    public int updateDocAchievement(@RequestBody List<PaymentInfo> list, Model model) throws Exception {
        try {
            return service.updateDocAchievement(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    @RequestMapping(value = "/updateDealDocAchievement")
    @ResponseBody
    public int updateDealDocAchievement(@RequestBody List<PaymentInfo> list, Model model) throws Exception {
        try {
            return service.updateDealDocAchievement(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    @RequestMapping(value = "/backAllOrder1")
    @ResponseBody
    public int backAllOrder1(@RequestBody List<PaymentInfo> list, Model model) throws Exception {
        String customerId = list.get(0).getCustomerId();
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        String tableName1 = tableHead + "deal_info";
        model.addAttribute("customerId", customerId);
        try {
            String dealSql = "select id,customer_id from " + tableName1 + " where id = '" + list.get(0).getId2() + "'";
            PaymentInfo dealInfo = sqlMapper.selectOne(dealSql, PaymentInfo.class);
            String selectsql = "select id,deal_status from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " where id='" + dealInfo.getCustomerId() + "' limit 0,1";
            CustomerInfo customerInfo = sqlMapper.selectOne(selectsql, CustomerInfo.class);
            if (customerInfo != null) {
                if (!"已结账".equals(customerInfo.getDealStatus())) {
                    String sql = "update " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "customer_info", 0) + " set deal_status='已开单' where id='" + customerId + "'";
                    sqlMapper.update(sql);
                }
            }
            return service.backAllOrder1(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    @RequestMapping(value = "/deleteOrder")
    @ResponseBody
    public int deleteOrder(String customerId, String reservationsId, String operationTime, String getTime) {
        if (ToolUtil.isEmpty(customerId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(reservationsId)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String sql = "select id from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " " +
                " where customer_id = '" + customerId + "' and reservations_id = '" + reservationsId + "'" +
                " and get_time = '" + getTime + "' and deal_status='已结账'";
        List<RechargeInfo> list = sqlMapper.selectList(sql, RechargeInfo.class);
        if (list.size() > 0) {  //单已结账tc界面未刷新
            return 111;
        }
        return service.deleteOrder(customerId, reservationsId, operationTime, getTime);
    }

    @RequestMapping(value = "/to_openBileLists/{id}/{tabId}")
    public String to_openBileLists(@PathVariable String id, @PathVariable String tabId, Model model) throws Exception {
        if (ToolUtil.isEmpty(id)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        String tableName = tableHead + "recharge_info";
        CustomerInfo customerInfo1 = customerService.selectOneById(id); //当前记录的患者信息
        CustomerInfo customerInfo = customerService.selectOneByReservationsId(customerInfo1.getReservationsId());
        String sqlresrvations = "select id from reservations_info where id='" + customerInfo.getReservationsId() + "' limit 0,1";
        ReservationsInfo reservationsInfo = sqlMapper.selectOne(sqlresrvations, ReservationsInfo.class);
        if (reservationsInfo == null) {
            reservationsService.insert(customerInfo);
        }
        //看诊详情
        List<ConsultantInfo> consultantInfoList = service.getConsultantInfoList2(customerInfo.getReservationsId());
        for (ConsultantInfo info1 : consultantInfoList) {
            if (info1.getDiagnosisTime() != "" && info1.getDiagnosisTime() != null) {
                info1.setDiagnosisTimeOne(info1.getDiagnosisTime().substring(0, 10));
                info1.setDiagnosisTimeTwo(info1.getDiagnosisTime().substring(11, info1.getDiagnosisTime().length()));
            }
        }
        //成交记录 ,结账记录
        String tableName1 = tableHead + "deal_info";
        String sql = "select t1.id as id,t1.customer_id as customerId ,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus, t1.tc_name as tcName ,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime," +
                "sum(t1.service_actual_pay)+sum(t1.deposit_amount) as totleServiceActualPay," +
                "if((select sum(folding_pay) from " + tableName1 + " a where a.operation_time=t1.operation_time and a.operation_time=a.get_time) is null,0," +
                " (select sum(folding_pay) from " + tableName1 + " a where a.operation_time=t1.operation_time and a.operation_time=a.get_time)) as totleFoldingPay," +
                "(select sum(deduction_amount) from " + tableName1 + " a where a.operation_time=t1.operation_time and a.operation_time=a.get_time) as totaldeductionAmount," +
                "sum(t1.arrears) as totleArrears,group_concat(distinct t1.remarks) as remarks," +
                " t1.folding_pay as foldingPay,t1.pay_service as payService," +
                "t2.id as payServiceId,t2.department as department,t2.first_level_project as firstLevelProject,t1.arrears as arrears," +
                "group_concat(distinct t2.pay_service_name) as payServiceName, t2.pay_service_price_one as payServicePriceOne, t2.pay_service_price_two as payServicePriceTwo," +
                "left(t1.get_time,19) as getTime ," +
                " group_concat(distinct t1.doctor) as doctor,t1.orthodontic_number as orthodonticNumber " +
                " from " + tableName1 + " t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id " +
                " where t1.reservations_id='" + customerInfo.getReservationsId() + "' and pay_service_id is not null and t1.deal_status='已结账' group by  operationTime order by operationTime desc";
        List<PayServicedInfo> payServiceInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        //未结账记录
        String sqlno = "SELECT t1.id AS id,t1.customer_id AS customerId,t1.reservations_id AS reservationsId,t1.tc_name as tcName ,t1.outpatient_name AS outpatientName," +
                " t1.deal_status AS dealStatus, left(t1.get_time,19) as getTime,t1.operation_time as operationTime, sum(t1.service_actual_pay) as serviceActualPay, " +
                " GROUP_CONCAT(DISTINCT t1.remarks) AS remarks,GROUP_CONCAT(DISTINCT t1.doctor) AS doctor" +
                " FROM " + tableName1 + " t1 LEFT JOIN " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 ON t1.pay_service_id=t2.id" +
                " WHERE t1.reservations_id='" + customerInfo.getReservationsId() + "' AND (t1.deal_status='已开单' or t1.deal_status='待审核') " +
                " group by t1.get_time order by t1.get_time desc ";
        List<PayServicedInfo> NoPayServiceInfoList = sqlMapper.selectList(sqlno, PayServicedInfo.class);
        String getTime = "";
        for (PayServicedInfo info : payServiceInfoList) {
            if (info != null) {
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                getTime = info.getGetTime();
            }
            if (StringUtil.isNotEmpty(info.getOrthodonticNumber())) {
                customerInfo.setOrthodonticNumber(info.getOrthodonticNumber());
            }
        }
        //到过的店
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + customerInfo.getReservationsId() + "' ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        String customerTables = "";
        String customerTablesd = "";
        List<Map<String, String>> deptList = new ArrayList<Map<String, String>>();
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + customerInfo.getReservationsId() + "' ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + customerInfo.getReservationsId() + "' ";
                customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                        "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                        "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                        "t.appointment_pay_service as appointmentPayService " +
                        "from " + head + "customer_info t where reservations_id='" + customerInfo.getReservationsId() + "' ";
                customerTablesd += "select id,appointment_pay_service,arrival_time,archiving,field_tc from " + head + "customer_info t where reservations_id='" + customerInfo1.getReservationsId() + "' and arrival_time is not null";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + customerInfo.getReservationsId() + "' union ";
                rechargeTables += "select document_number,deal_id,id2,recharge_people,operation_time,remarks from " + head + "recharge_info where reservations_id='" + customerInfo.getReservationsId() + "' union ";
                customerTables += "select customer_name as customerName, left(t.create_appointment_time,19) as createAppointmentTime,left(t.start_time,19) as startTime,t.id as id,t.remarks_reservation as remarksReservation," +
                        "t.reservations_id as reservationsId,t.doctor as doctor,t.field_tc as fieldTc,t.remarks as remarks,t.arrival_status as arrivalStatus," +
                        "t.archiving as archiving,t.arrival_store as arrivalStore,t.customer_status as customerStatus,t.status as status," +
                        "t.appointment_pay_service as appointmentPayService " +
                        "from " + head + "customer_info t where reservations_id='" + customerInfo.getReservationsId() + "' union ";
                customerTablesd += "select id,appointment_pay_service,arrival_time,archiving,field_tc " +
                        "from " + head + "customer_info t where reservations_id='" + customerInfo.getReservationsId() + "' " +
                        "and arrival_time is not null union ";
            }
        }
        FirstCustomerInfo customerInfoFirst = customerService.selectOneByReservationsIdFirst(customerInfo1.getReservationsId(), customerTablesd);  //首次到店患者信息
        //就诊记录
        String sql11 = "select * from (" + customerTables + ") t order by t.startTime desc";
        List<CustomerInfo> customerInfoList = sqlMapper.selectList(sql11, CustomerInfo.class);
        for (CustomerInfo infos : customerInfoList) {
            infos.setOutpatientName(ShiroKit.getUser().getOutpatientName());
            if (infos.getStartTime() != "" && infos.getStartTime() != null) {
                infos.setStartTimeOne(infos.getStartTime().substring(0, 10));
                infos.setStartTimeTwo(infos.getStartTime().substring(11, infos.getStartTime().length()));
            }
        }
        //账单记录
        Double totalServiceActualPay = 0.00;
        Double totalArrears = 0.00;
        String custDealSql = "select t1.id2 as id2," +
                "t1.reservations_id as reservationsId, " +
                "t1.outpatient_name as outpatientName," +
                "left(t1.operation_time,19) as operationTime," +
                "t1.deal_status as dealStatus," +
                "GROUP_CONCAT( distinct (t1.doctor)) as doctor,GROUP_CONCAT( distinct (t1.nurse)) as nurse," +
                "t1.tc_name as tcName," +
                "sum(t1.service_actual_pay+t1.arrears+t1.deduction_amount+t1.deposit_amount) AS foldingPay," +
                "sum(t1.service_actual_pay) as serviceActualPay," +
                "sum(t1.arrears) as arrears," +
                "sum(t1.deduction_amount) as deductionAmount," +
                "sum(t1.deposit_amount) as depositAmount," +
                "t1.remarks as  remarks,(select distinct(r1.document_number) from (" + rechargeTables + ") r1 where r1.deal_id=t1.id group by r1.deal_id) as documentNumber " +
                "from (" + dealTables + ") t1 where t1.deal_status='已结账' and  t1.reservations_id = '" + customerInfo.getReservationsId() + "' " +
                "group by t1.operation_time order by t1.operation_time desc";
        List<PayServicedInfo> custDealList = sqlMapper.selectList(custDealSql, PayServicedInfo.class);
        for (int i = 0; i < custDealList.size(); i++) {
            List<PayServicedInfo> dealList = getDealInfoList(custDealList.get(i).getOperationTime(), custDealList.get(i).getReservationsId());
            custDealList.get(i).setDealList(dealList);
            custDealList.get(i).setDocumentNumber(dealList.get(0).getDocumentNumber());
            totalServiceActualPay = totalServiceActualPay + custDealList.get(i).getServiceActualPay() + custDealList.get(i).getDepositAmount();
            totalArrears = totalArrears + custDealList.get(i).getArrears();
        }
        //支付记录
        String payDealSql = "select t.outpatient_name as outpatientName,left(t.get_time,19) as getTime,t.is_do as isDo,t.tc_name as tcName,t.deal_status as dealStatus,t2.recharge_people " +
                "as rechargePeople,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay) as serviceActualPay, sum(t.arrears) as arrears," +
                "(sum(t.folding_pay)+sum(t.arrears)) as tkfoldingPay,sum(t.deduction_amount) AS deductionAmount,sum(t.deposit_amount) as depositAmount," +   //退款=应收+欠费
                "t2.remarks as remarks from (" + dealTables + ") t left join (select b.id2 as id2, b.recharge_people as recharge_people, " +
                "b.operation_time as operation_time,b.remarks as remarks from (" + rechargeTables + ") b group by b.operation_time) t2 on t.get_time = t2.operation_time where " +
                "t.reservations_id='" + customerInfo.getReservationsId() + "' and t.deal_status = '已结账' group by t.get_time order by t.get_time desc;";
        List<PayServicedInfo> payDealList = sqlMapper.selectList(payDealSql, PayServicedInfo.class);
        Double totalPayDealActualPay = 0.00;
        Double totalPayDealArrears = 0.00;
        for (int i = 0; i < payDealList.size(); i++) {
            totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getServiceActualPay())).doubleValue();
            totalPayDealActualPay = BigDecimal.valueOf(totalPayDealActualPay).add(BigDecimal.valueOf(payDealList.get(i).getDepositAmount())).doubleValue();

            totalPayDealArrears = BigDecimal.valueOf(totalPayDealArrears).add(BigDecimal.valueOf(payDealList.get(i).getArrears())).doubleValue();
        }
        //欠费记录
        String arrearsDealSql = "select * from (select t.id2 as id2,t.reservations_id as reservationsId,t.customer_id as customerId," +
                "t.deal_status as dealStatus,t.outpatient_name as outpatientName,t.remarks as remarks,left(t.operation_time,19) as operationTime," +
                "t.doctor as doctor,t.tc_name as tcName,t.nurse as nurse,sum(t.deposit_amount) AS depositAmount,sum(t.deduction_amount) AS deductionAmount,sum(t.folding_pay) as foldingPay,sum(t.service_actual_pay)+sum(t.deposit_amount) as serviceActualPay,sum(t.arrears) as arrears,group_concat(distinct t1.pay_service_name) as payServiceName " +
                "from (" + dealTables + ") t  left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 on t.pay_service_id=t1.id where t.deal_status='已结账' group by t.operation_time ) t1 where t1.reservationsId = '" + customerInfo.getReservationsId() + "' and t1.arrears  >0 order by t1.operationTime desc;";
        List<PayServicedInfo> arrearsDealList = sqlMapper.selectList(arrearsDealSql, PayServicedInfo.class);
        Double totalArrearsAmount = 0.00;
        for (int i = 0; i < arrearsDealList.size(); i++) {
            totalArrearsAmount += arrearsDealList.get(i).getArrears();
        }
        //通过预约ID  执行记录
        String sqll = "select t1.id as id,t1.id2 as id2,t1.customer_id as customerId,t1.reservations_id as reservationsId ,t1.outpatient_name as outpatientName , t1.deal_status  as dealStatus,t1.pay_service_num as payServiceNum ," +
                " t1.service_actual_pay as serviceActualPay ,left(t1.operation_time,19) as operationTime,left(t3.recharge_time,19) as rechargeTime,t1.remarks as remarks," +
                " t1.arrears as arrears,t2.pay_service_name as payServiceName,t1.get_time as getTime from (" + dealTables + ") t1 left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t2 on t1.pay_service_id=t2.id left join " + tableName + " t3 on t3.id2=t1.id2 " +
                " where t1.reservations_id='" + customerInfo.getReservationsId() + "' and  t1.deal_status ='已结账'  group by  operationTime order by operationTime desc";
        List<PayServicedInfo> payServiceInfoList1 = sqlMapper.selectList(sqll, PayServicedInfo.class);
        Double totleReceivable = 0.00;
        Double totleFoldingPay = 0.00;   //
        Double totleServiceActualPay = 0.00; //总实收
        Double accountArrears = 0.00;  //总欠款
        for (PayServicedInfo info : payServiceInfoList1) {
            if (info != null) {
                if (info.getPayServiceId() != null && info.getPayServiceId() != "") {
                    if (info.getFoldingPay() != null) {
                        totleFoldingPay += info.getFoldingPay();
                    }
                    //总实收
                    if (info.getServiceActualPay() != null) {
                        totleServiceActualPay += info.getServiceActualPay();
                    }
                    //总欠款
                    if (info.getArrears() != null) {
                        accountArrears += info.getArrears();
                    }
                    info.setTotleReceivable(totleReceivable);
                    info.setTotleFoldingPay(totleFoldingPay);
                    info.setTotleServiceActualPay(totleServiceActualPay);
                    info.setAccountArrears(accountArrears);
                }
                if (info.getOperationTime() != "" && info.getOperationTime() != null) {
                    info.setOperationTimeOne(info.getOperationTime().substring(0, 10));
                    info.setOperationTimeTwo(info.getOperationTime().substring(11, info.getOperationTime().length()));
                }
                if (info.getRechargeTime() != "" && info.getRechargeTime() != null) {
                    info.setRechargeTimeOne(info.getRechargeTime().substring(0, 10));
                    info.setRechargeTimeTwo(info.getRechargeTime().substring(11, info.getRechargeTime().length()));
                }
            }
        }
        //病例记录
        //1、先查询当前客户的所有病例记录
        String caseInfoSql = "select left(t1.finnally_time,19) as finnallyTime1,t1.* from case_info t1  " +
                "where t1.reservations_id='" + customerInfo.getReservationsId() + "'order by t1.finnally_time desc";
        List<CaseInfo> caseInfoList = sqlMapper.selectList(caseInfoSql, CaseInfo.class);
        for (CaseInfo infos : caseInfoList) {
            if (infos != null) {
                if (infos.getOperationTime() != "" && infos.getOperationTime() != null) {
                    infos.setOperationTime(infos.getOperationTime().substring(0, 10));
                }
                String sql3 = "select pay_service_name as payServiceName from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " where id ='" + infos.getPayServiceId() + "'";
                CaseInfo caseInfo1 = sqlMapper.selectOne(sql3, CaseInfo.class);
                if (caseInfo1 == null) {
                    continue;
                }
                infos.setPayServiceName(caseInfo1.getPayServiceName());
            }
        }
        //2.循环遍历每个病例记录，给对应病例的牙位属性赋值
        for (CaseInfo caseInfo : caseInfoList) {
            //然后查询牙位集合
            String toothSIteSql = "select * from tooth_site t1 where t1.case_id='" + caseInfo.getId() + "' ";
            List<ToothSite> toothSiteList = sqlMapper.selectList(toothSIteSql, ToothSite.class);
            for (ToothSite toothSite : toothSiteList) {
                //根据对应的牙位类型，给病例实体的对应牙位属性赋值
                if (toothSite.getYaType().equals("1")) {
                    caseInfo.getToothSite1List().add(toothSite);
                }
                if (toothSite.getYaType().equals("2")) {
                    caseInfo.getToothSite2List().add(toothSite);
                }
                if (toothSite.getYaType().equals("3")) {
                    caseInfo.getToothSite3List().add(toothSite);
                }
                if (toothSite.getYaType().equals("4")) {
                    caseInfo.getToothSite4List().add(toothSite);
                }
                if (toothSite.getYaType().equals("5")) {
                    caseInfo.getToothSite5List().add(toothSite);
                }
            }
        }
        String selectsql = "select id from today_operation_info where reservations_id='" + customerInfo.getReservationsId() + "' and " +
                " left(operation_time,10)='" + DateTimeKit.now().substring(0, 10) + "' and operation_people='" + ShiroKit.getUser().getAccount() + "'" +
                " and store='" + customerInfo.getArrivalStore() + "'";
        List<DoctorOperationInfo> doctorOperationInfo = sqlMapper.selectList(selectsql, DoctorOperationInfo.class);
        if (doctorOperationInfo.size() == 0) { //是否操作过该患者
            //插入 今日操作数据 前台
            String insertSql = " INSERT INTO today_operation_info  (customer_id,reservations_id,szm,operation_people,customer_name, " +
                    " customer_sex,customer_age, customer_phone,store,operation_time) values  " +
                    " ('" + id + "','" + customerInfo.getReservationsId() + "','" + customerInfo.getSzm() + "','" + ShiroKit.getUser().getAccount() + "','" + customerInfo.getCustomerName() + "','" + customerInfo.getCustomerSex() + "', " +
                    " '" + customerInfo.getCustomerAge() + "','" + customerInfo.getCustomerPhone() + "','" + customerInfo.getArrivalStore() + "','" + DateTimeKit.now() + "') ";
            sqlMapper.insert(insertSql);
        } else {
            String updatesql = "update today_operation_info set operation_time='" + DateTimeKit.now() + "' where reservations_id='" + customerInfo.getReservationsId() + "' and " +
                    " left(operation_time,10)='" + DateTimeKit.now().substring(0, 10) + "' and operation_people='" + ShiroKit.getUser().getAccount() + "' " +
                    " and store='" + customerInfo.getArrivalStore() + "'";
            sqlMapper.update(updatesql);
        }
        //获取抵扣方式
        String checkoutSql = "select id,checkout_method from checkout_method_info where type in ('1','2')";
        List<CheckoutMethodInfo> checkoutMethodList = sqlMapper.selectList(checkoutSql, CheckoutMethodInfo.class);
        String checkoutMethodHtml = "";
        for (int i = 0; i < checkoutMethodList.size(); i++) {
            checkoutMethodHtml += "<option value='" + checkoutMethodList.get(i).getId() + "'>" + checkoutMethodList.get(i).getCheckoutMethod() + "</option>";
        }
        if (ShiroKit.getUser().getRefereeShipInfoList() == null) {
            String getListSql = "select id,referee_ship as refereeShip from referee_ship_info order by num asc;";
            List<RefereeShipInfo> refereeShipList = sqlMapper.selectList(getListSql, RefereeShipInfo.class);
            ShiroKit.getUser().setRefereeShipInfoList(refereeShipList);
        }
        if (ShiroKit.getUser().getOccupationInfoList() == null) {
            String getListSql = "select id, occupation from occupation_info where isshow=1 order by num asc;";
            List<OccupationInfo> occupationList = sqlMapper.selectList(getListSql, OccupationInfo.class);
            ShiroKit.getUser().setOccupationInfoList(occupationList);
        }
        if (ShiroKit.getUser().getTcList() == null) {
            String getListSql = " select a.id as id,a.account as tcName from user a left join  dept b on a.deptid like CONCAT('%',b.id,',','%') left join role c on a.roleid=c.id " +
                    " where b.abbreviation='" + tableHead + "' and c.name like CONCAT('%','tc','%') ";
            List<TcInfo> tcList = sqlMapper.selectList(getListSql, TcInfo.class);
            ShiroKit.getUser().setTcList(tcList);
        }
        String jfbalancesql = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from return_cash_info where reservations_id='" + customerInfo.getReservationsId() + "'";
        ScoreDealInfo scoreDealInfo = sqlMapper.selectOne(jfbalancesql, ScoreDealInfo.class);
        BigDecimal b1 = new BigDecimal(Double.parseDouble(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Proportional", 0) + ""));
        BigDecimal b2 = new BigDecimal(Double.parseDouble(scoreDealInfo.getScoreBalance()));
        Double jfbalance = b1.multiply(b2).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
        String selsql = "select reservations_id,members_num from members_user where reservations_id='" + customerInfo.getReservationsId() + "' limit 1";
        MembersUser membersUser = sqlMapper.selectOne(selsql, MembersUser.class);
        if (membersUser != null) {
            customerInfo.setMembersNum(membersUser.getMembersNum());
        }
        model.addAttribute("tcList", ShiroKit.getUser().getTcList());
        model.addAttribute("occupationList", ShiroKit.getUser().getOccupationInfoList());
        model.addAttribute("refereeShipList", ShiroKit.getUser().getRefereeShipInfoList());
        model.addAttribute("checkoutMethodHtml", checkoutMethodHtml);
        model.addAttribute("custDealList", custDealList);
        model.addAttribute("payDealList", payDealList);
        model.addAttribute("arrearsDealList", arrearsDealList);
        model.addAttribute("totalServiceActualPay", totalServiceActualPay);
        model.addAttribute("totalArrears", totalArrears);
        model.addAttribute("totalPayDealActualPay", totalPayDealActualPay);
        model.addAttribute("totalPayDealArrears", totalPayDealArrears);
        model.addAttribute("totalArrearsAmount", totalArrearsAmount);
        model.addAttribute("userName", ShiroKit.getUser().getAccount());
        model.addAttribute("roleName", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "RoleName", 0));
        model.addAttribute("roleNameLogin", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "RoleName", 0));
        model.addAttribute("dealyesLogin", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Dealyes", 0));
        model.addAttribute("payServiceInfoList", payServiceInfoList); //结账记录
        model.addAttribute("payServiceInfoList1", payServiceInfoList1);   //执行记录
        model.addAttribute("NoPayServiceInfoList", NoPayServiceInfoList); //未结账记录
        model.addAttribute("reservationsId", customerInfo.getReservationsId());
        model.addAttribute("customerId", id);
        model.addAttribute("customerInfo", customerInfo);
        model.addAttribute("customerInfoFirst", customerInfoFirst);  //首次到店患者信息
        model.addAttribute("recentlyCustomerStatus", customerPublicService.getNewCustomerStatus(customerInfo.getReservationsId()));
        model.addAttribute("getTime", getTime);
        model.addAttribute("consultantInfo", customerInfo);
        model.addAttribute("tabId", tabId);
        model.addAttribute("caseInfoList", caseInfoList);
        model.addAttribute("consultantInfoList", consultantInfoList); //看诊记录
        model.addAttribute("customerInfoList", customerInfoList);//就诊记录
        model.addAttribute("auditswitch", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Auditswitch", 0));
        model.addAttribute("jfbalance", jfbalance);
        model.addAttribute("khjfbalance", jfbalance);
        String jfbalancesql1 = "select if(sum(score_balance) is null,0,sum(score_balance)) as scoreBalance from score_deal where reservations_id='" + customerInfo.getReservationsId() + "'";
        ScoreDealInfo scoreDealInfo1 = sqlMapper.selectOne(jfbalancesql1, ScoreDealInfo.class);
        model.addAttribute("jfbalancea", scoreDealInfo1.getScoreBalance());
        String getMoneyBalance = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' limit 1";
        MembersUser membersUser1 = sqlMapper.selectOne(getMoneyBalance, MembersUser.class);
        model.addAttribute("moneyBalance", membersUser1.getMoneyBalance());
        String getOrthodonticsMoney = "select if(sum(a.actual_account_balance) is null,0,sum(a.actual_account_balance)) as moneyBalance from reservemoney_orthodontics_deal a " +
                " where a.reservations_id='" + customerInfo.getReservationsId() + "' limit 1";
        MembersUser membersUser2 = sqlMapper.selectOne(getOrthodonticsMoney, MembersUser.class);
        model.addAttribute("orthodonticsMoney", membersUser2.getMoneyBalance());
        model.addAttribute("khmoneyBalance", membersUser1.getMoneyBalance());
        model.addAttribute("arrivalTimeStr", customerInfo1.getArrivalTime());
        model.addAttribute("customerInfo1", customerInfo1);
        model.addAttribute("treatmentUnit", customerInfo1.getTreatmentUnit());
        model.addAttribute("isRefundAdmin", redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "IsRefundAdmin", 0));
        String vipLevelName = memberProductService.getVipLevelName(customerInfo.getReservationsId());
        model.addAttribute("vipLevelName", vipLevelName);
        String updasql = "update members_user set vip_level_name='" + vipLevelName + "' where reservations_id='" + customerInfo.getReservationsId() + "'";
        sqlMapper.update(updasql);
        return PREFIX + "customer_customerDetails.html";
    }

    public List<PayServicedInfo> getDealInfoList(String operationTime, String reservationsId) throws Exception {
        if (ToolUtil.isEmpty(operationTime)) {
            throw new BussinessException(BizExceptionEnum.REQUEST_NULL);
        }
        String abbreviationSql = "select abbreviation from distinction_customer_status where reservations_id = '" + reservationsId + "' and status=1 ";
        List<String> abbreviationList = sqlMapper.selectList(abbreviationSql, String.class);
        String dealTables = "";
        String rechargeTables = "";
        for (int i = 0; i < abbreviationList.size(); i++) {
            String head = abbreviationList.get(i);
            if (i == abbreviationList.size() - 1) {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' ";
            } else {
                dealTables += "select * from " + head + "deal_info where reservations_id='" + reservationsId + "' union ";
                rechargeTables += "select * from " + head + "recharge_info where reservations_id='" + reservationsId + "' union ";
            }
        }
        String sql = "select t1.id as id,t1.outpatient_name as outpatientName,left(t1.get_time,19) as getTime,t1.is_do as isDo,t1.deal_status as dealStatus," +
                "t1.doctor1 as doctor,t1.nurse1 as nurse,t1.order_discount as orderDiscount,t1.deductionAmount as deductionAmount,t1.remarks as remarks,t1.serviceActualPay as serviceActualPay," +
                "t2.recharge_people as rechargePeople,t2.checkout_method as checkoutMethod," +
                "t2.document_number as documentNumber,t1.tc_name as tcName,left(t1.operation_time,19) as operationTime from (select t.id2 as id,t.outpatient_name as outpatient_name," +
                "t.get_time as get_time,t.tc_name as tc_name,t.is_do as is_do,t.deal_status as deal_status,GROUP_CONCAT(DISTINCT t.doctor) AS doctor1,GROUP_CONCAT(DISTINCT t.nurse) AS nurse1,(sum(t.service_actual_pay)+sum(t.deposit_amount)) as serviceActualPay," +
                "t.order_discount as order_discount,sum(t.deduction_amount) as deductionAmount,t.remarks as remarks,t.operation_time as operation_time from (" + dealTables + ") t where t.operation_time='" + operationTime + "' and t.reservations_id " +
                "= '" + reservationsId + "' and t.deal_status='已结账' group by t.get_time) t1 left join " + "(select b.recharge_people as recharge_people," +
                "b.document_number as  document_number,group_concat(distinct b.checkout_method) as checkout_method, b.operation_time as operation_time from (" + rechargeTables + ") b " +
                "group by b.operation_time) t2 on t1.get_time = t2.operation_time order by t1.get_time desc";
        List<PayServicedInfo> payServiceList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return payServiceList;
    }

    //收欠费使用储值或定金得到本单项目
    @RequestMapping(value = "/inquireAboutArrears", method = RequestMethod.POST)
    @ResponseBody
    public List<PayServicedInfo> inquireAboutArrears(String operationTimes, String reservationsId) throws Exception {
        String sql = "select t1.pay_service_name as payServiceName,t.pay_service as payService,t.pay_service_num as payServiceNum," +
                " sum(t.arrears) as arrears,t.id2," +
                " t.operation_time as operationTime,t.get_time as getTime,t.id " +
                " from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " t left join " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "pay_service_info", 0) + " t1 on t.pay_service_id = t1.id " +
                " where t.reservations_id = '" + reservationsId + "' and t.operation_time = '" + operationTimes + "' " +
                " group by id2";
        List<PayServicedInfo> dealInfoList = sqlMapper.selectList(sql, PayServicedInfo.class);
        return dealInfoList;
    }


    /**
     * 得到开单的状态
     */
    @RequestMapping(value = "/getDealStatus", method = RequestMethod.POST)
    @ResponseBody
    public int getDealStatus(String reservationsId, String operationTime, String customerId, Model model) {
        String sql1 = "select id from " + redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "deal_info", 0) + " " +
                " where reservations_id = '" + reservationsId + "' and operation_time='" + operationTime + "' " +
                " and customer_id = '" + customerId + "' and deal_status='已结账'";
        List<RechargeInfo> list = sqlMapper.selectList(sql1, RechargeInfo.class);
        if (list.size() > 0) {  //单已结账tc界面未刷新
            return 111;
        }
        return 1;
    }

    /**
     * 跳转到医生排班
     */
    @RequestMapping("/tc_scheduling")
    public String tc_scheduling(Model model) {
        String tableHead = redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "Abbreviation", 0) + "";
        if (tableHead == "" || tableHead == null) {
            LogManager.me().executeLog(LogTaskFactory.exitLog(ShiroKit.getUser().getId(), HttpKit.getIp()));
            ShiroKit.getSubject().logout();
            return "/login.html";
        }
        return PREFIX + "tc_scheduling.html";
    }


    @RequestMapping(value = "/consultantExport")
    public void reservationsExport(@Valid SearchCustomerInfo searchInfo, HttpServletResponse response) {
        try {
            List<CustomerInfo> list = service.consultantExportt(searchInfo);
            // 解析成excel文件
            // 第一步，创建一个webbook，对应一个Excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，在wb中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("前端导出");
            for (int i = 0; i < 17; i++) {
                sheet.setColumnWidth(i, 24 * 256);
            }
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);// 创建一个居中格式
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("病历号");
            cell.setCellStyle(style);
            cell = row.createCell(1);
            cell.setCellValue("客户姓名");
            cell.setCellStyle(style);
            cell = row.createCell(2);
            cell.setCellValue("电话");
            cell.setCellStyle(style);
            cell = row.createCell(3);
            cell.setCellValue("性别");
            cell.setCellStyle(style);
            cell = row.createCell(4);
            cell.setCellValue("年龄");
            cell.setCellStyle(style);
            cell = row.createCell(5);
            cell.setCellValue("已交金额");
            cell.setCellStyle(style);
            cell = row.createCell(6);
            cell.setCellValue("欠费");
            cell.setCellStyle(style);
            cell = row.createCell(7);
            cell.setCellValue("客户状态");
            cell.setCellStyle(style);
            cell = row.createCell(8);
            cell.setCellValue("看诊状态");
            cell.setCellStyle(style);
            cell = row.createCell(9);
            cell.setCellValue("开单状态");
            cell.setCellStyle(style);
            cell = row.createCell(10);
            cell.setCellValue("成交状态");
            cell.setCellStyle(style);
            cell = row.createCell(11);
            cell.setCellValue("科室");
            cell.setCellStyle(style);
            cell = row.createCell(12);
            cell.setCellValue("TC");
            cell.setCellStyle(style);
            cell = row.createCell(13);
            cell.setCellValue("跟踪状态");
            cell.setCellStyle(style);
            cell = row.createCell(14);
            cell.setCellValue("消费状态");
            cell.setCellStyle(style);
            cell = row.createCell(15);
            cell.setCellValue("回访时间");
            cell.setCellStyle(style);
            cell = row.createCell(16);
            cell.setCellValue("初诊时间");
            cell.setCellStyle(style);
            cell = row.createCell(17);
            cell.setCellValue("到店时间");
            cell.setCellStyle(style);
            // 填充数据
            for (int i = 0; i < list.size(); i++) {
                CustomerInfo map = list.get(i);
                row = sheet.createRow(i + 1);
                // 第四步，创建单元格，并设置值
                for (int j = 0; j <= 17; j++) {
                    row.createCell(j).setCellStyle(style);
                }
                row.getCell(0).setCellValue(map.getMedicalRecordNumber());
                row.getCell(1).setCellValue(map.getCustomerName());
                if (Integer.parseInt(redisTemplate.opsForList().index(ShiroKit.getUser().getAccount() + "PhonePrivilegesexp", 0) + "") == 1) {
                    row.getCell(2).setCellValue(map.getCustomerPhone());
                } else {
                    row.getCell(2).setCellValue("无");
                }
                row.getCell(3).setCellValue(map.getSex());
                row.getCell(4).setCellValue(map.getCustomerAge());
                row.getCell(5).setCellValue(map.getActualAccountBalance() + map.getMoneyBalance());
                row.getCell(6).setCellValue(map.getAccountArrears());
                row.getCell(7).setCellValue(map.getCustomerStatus());
                row.getCell(8).setCellValue(map.getDiagnosisStatus());
                if ("未开单".equals(map.getDealStatus())) {
                    row.getCell(9).setCellValue("未开单");
                } else if ("已开单".equals(map.getDealStatus())) {
                    row.getCell(9).setCellValue("已开单");
                } else {
                    row.getCell(9).setCellValue("已结账");
                }
                row.getCell(10).setCellValue(map.getIsdealStatus());
                row.getCell(11).setCellValue(map.getTreatmentUnit());
                row.getCell(12).setCellValue(map.getFieldTc());
                row.getCell(13).setCellValue(map.getFollowStatus());
                row.getCell(14).setCellValue(map.getConsumptionStatus());
                row.getCell(15).setCellValue(map.getAppointmentTime());
                row.getCell(16).setCellValue(map.getFirstVisitDate());
                row.getCell(17).setCellValue(map.getArrivalTime());
            }
            String date = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new java.util.Date());
            String filename = "data" + date + ".xls";
            response.setHeader("Content-Disposition",
                    "attachment; filename=\"" + URLEncoder.encode(filename, "UTF-8") + "\"");
            response.setContentType("application/octet-stream;charset=UTF-8");
            OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}